Retrieving info in Excel (2000)

Topic

New Reply

(Edited by HansV to replace extremely wide screenshot that caused horizontal scrolling by a narrower one.)

Hello,

I have just created a spreadsheet and would like to know the easiest and quickest way to retrieve information from it.
i.e find what reference number relates to what document number.
I found using auto filters a bit cumbersome is there an easier way?

Regards
Justin

Reply | Quote

Replies

I am not sure what you are trying to do, I don’t see a document number, only a name. Could you give more details?

Some thoughts:
Individually you can use VLOOKUP to get the “ref No” given a “Document TItle”

To get a “title” given a “ref no” will require a combo of index and match. See post 244408

To make a table of “title” vs ref no” (assuming your current list has duplicates, could be done with a adv filter and extracting the unique items

To get summary info, you could use a pivot table.

Steve

Reply | Quote

Hi,

Sorry when l am referring to number l mean Ref No.
For any reference number called up l will require the relevent information along the top row in blue.
What l would like is to call up a Ref No and bring up the information which relates to this.
i.e Document Name etc.

I tried to setup a pivot table but got an error.

Regards
Justin

Reply | Quote

Using INDEX with the row and column you can pull out any row.
Given a reference number you can use the technique in post 244408 to get info on any of the items in any of the columns in that row. [The technique uses match to get the row for the entered reference number] and then uses INDEX.

How do you want to “call up the reference”.

I am not trying to be “vague”, I give general answers to general questions, and more specific answers to more specific questions. Your question is a little vague on what you are doing and what you want:
info on a specific row given a ref no (assumes 1 match as it will “find” the first match)
Summary of all the info (pivot table: error seems to indicate that excel does not like one of the “headers” not sure which one, not enough info.

Could you be a little more specific about what you have and what you want?
Steve

Reply | Quote

It appears that Vlookup might be want l want.

Not to sure how to use it though.
Please could you explain , as did not quite understand in example.
What l would like to do is to enable the user to type in a Ref No and retrieve all the relevant details across the top row which relate to the number entered.
See attachment.

Justin

Reply | Quote

VLOOKUP would work directly if you had the ref no in the far left. As it is VLOOKUP will not get you what you want. DId you look at post 244408?

This has the method. Assume your “datatable” is in a sheet called “DataTable” and you want the output in a Sheet named “Output” In Output!A2 you have the refno to lookup.

The “row” of the match we will put in Output!B2:

=MATCH(A2,Datatable!$B$1:$B$100,0)

In Output!C1:H1 enter the numbers (representing the columns to lookup): 1,3,4,5,6,7

In Output!C2 enter (change datatable range as appropriate):

=INDEX(Datatable!$A$1:$G$100,$B2,C$1)

Copy Output!C2 to Output!D2:H2

Steve

Reply | Quote

I am a bit confused is is it possible to show me an illustrated example.

I have not used this command before and sort don’t really know where this command goes in the spreadsheet.
Some of the terms you have used l am not familiar with.
i.e “row” of the match and Output!C1

Is is possible to explain in less technical terms and maybe include a very simplified example.

I did look at Vlookup post but as l am not to familiar with it cannot be sure it is exactly want l want.

Justin.

Reply | Quote

As I stated I assumed that you had 2 sheets: 1 called “datatable” (which is where you got the screen shot) and another that I called “Output” where you wanted to place the “extracted info.

Output!A2 refers to Cell A2 of the sheet named output.
Just add the formulas I listed into the cells I list.

If you attach an example worksheet with an example table to lookup from, a cell to hold the refno to lookup, and tell me where you want the output, I will add the items into it.

Steve

Reply | Quote

Edited by HansV to remove unnecessary screenshot.

I don’t know where the formulas are to be placed in the spreadsheet.

Sending an example worksheet is to large than your website permits.

I only have one worksheet. I require the output to be in the same worksheet if this is possible.

I tried to produce a query from a spreadsheet this appears to do the trick work but l got an error: “This query cannot be edited by the Query Wizard.”
Is the method you are describing a simpler system?

Justin.

Reply | Quote

I just need an example workbook, it does not have to be the entire thing. Just give me an “extract” of the data, eg. the first 10 rows (and let me know how many rows you need to lookup in). Indicate on the sheet where you want th output to be and where you want the user’s to input the ref no.

I prefer to use your example than to spend the time making one up that will not necessarily be “relatedable” to what you are doing.

Steve

Reply | Quote

Will you please stop attaching these huge screenshots!
Next time, I will just delete it instead of editing it!
HansV

I require 340 rows to be queried.

I will require the user to input the info at the top of the screen.
see attachment.

Justin.

Reply | Quote

I still would prefer you to provide a sample sheet rather than a sample screenshot, but here is a “guess”.
I used row 1 as header. Row 2 has the input for the ref no. Rows 3-340 is the table, it just filled it with info, (change as desired) and row 342 is the “output”.
Enter a value in B2 and the values in A342-F342 will be “extracted” from the first row matching the ref no.

I was able to attach a sheet with 340 rows of info and keep under the limit.

Personally I would have put the output in the row above the header so that it always visible, but it is your sheet.

Steve

Reply | Quote

Thanks very much for that.

It appeared to work.

Thanks for your help.

Justin

Reply | Quote

Thanks very much for that.

It appeared to work.

Thanks for your help.

Justin

Reply | Quote

I still would prefer you to provide a sample sheet rather than a sample screenshot, but here is a “guess”.
I used row 1 as header. Row 2 has the input for the ref no. Rows 3-340 is the table, it just filled it with info, (change as desired) and row 342 is the “output”.
Enter a value in B2 and the values in A342-F342 will be “extracted” from the first row matching the ref no.

I was able to attach a sheet with 340 rows of info and keep under the limit.

Personally I would have put the output in the row above the header so that it always visible, but it is your sheet.

Steve

Reply | Quote

Will you please stop attaching these huge screenshots!
Next time, I will just delete it instead of editing it!
HansV

I require 340 rows to be queried.

I will require the user to input the info at the top of the screen.
see attachment.

Justin.

Reply | Quote

I just need an example workbook, it does not have to be the entire thing. Just give me an “extract” of the data, eg. the first 10 rows (and let me know how many rows you need to lookup in). Indicate on the sheet where you want th output to be and where you want the user’s to input the ref no.

I prefer to use your example than to spend the time making one up that will not necessarily be “relatedable” to what you are doing.

Steve

Reply | Quote

Edited by HansV to remove unnecessary screenshot.

I don’t know where the formulas are to be placed in the spreadsheet.

Sending an example worksheet is to large than your website permits.

I only have one worksheet. I require the output to be in the same worksheet if this is possible.

I tried to produce a query from a spreadsheet this appears to do the trick work but l got an error: “This query cannot be edited by the Query Wizard.”
Is the method you are describing a simpler system?

Justin.

Reply | Quote

As I stated I assumed that you had 2 sheets: 1 called “datatable” (which is where you got the screen shot) and another that I called “Output” where you wanted to place the “extracted info.

Output!A2 refers to Cell A2 of the sheet named output.
Just add the formulas I listed into the cells I list.

If you attach an example worksheet with an example table to lookup from, a cell to hold the refno to lookup, and tell me where you want the output, I will add the items into it.

Steve

Reply | Quote

I am a bit confused is is it possible to show me an illustrated example.

I have not used this command before and sort don’t really know where this command goes in the spreadsheet.
Some of the terms you have used l am not familiar with.
i.e “row” of the match and Output!C1

Is is possible to explain in less technical terms and maybe include a very simplified example.

I did look at Vlookup post but as l am not to familiar with it cannot be sure it is exactly want l want.

Justin.

Reply | Quote

VLOOKUP would work directly if you had the ref no in the far left. As it is VLOOKUP will not get you what you want. DId you look at post 244408?

This has the method. Assume your “datatable” is in a sheet called “DataTable” and you want the output in a Sheet named “Output” In Output!A2 you have the refno to lookup.

The “row” of the match we will put in Output!B2:

=MATCH(A2,Datatable!$B$1:$B$100,0)

In Output!C1:H1 enter the numbers (representing the columns to lookup): 1,3,4,5,6,7

In Output!C2 enter (change datatable range as appropriate):

=INDEX(Datatable!$A$1:$G$100,$B2,C$1)

Copy Output!C2 to Output!D2:H2

Steve

Reply | Quote

It appears that Vlookup might be want l want.

Not to sure how to use it though.
Please could you explain , as did not quite understand in example.
What l would like to do is to enable the user to type in a Ref No and retrieve all the relevant details across the top row which relate to the number entered.
See attachment.

Justin

Reply | Quote

Using INDEX with the row and column you can pull out any row.
Given a reference number you can use the technique in post 244408 to get info on any of the items in any of the columns in that row. [The technique uses match to get the row for the entered reference number] and then uses INDEX.

How do you want to “call up the reference”.

I am not trying to be “vague”, I give general answers to general questions, and more specific answers to more specific questions. Your question is a little vague on what you are doing and what you want:
info on a specific row given a ref no (assumes 1 match as it will “find” the first match)
Summary of all the info (pivot table: error seems to indicate that excel does not like one of the “headers” not sure which one, not enough info.

Could you be a little more specific about what you have and what you want?
Steve

Reply | Quote

Hi,

Sorry when l am referring to number l mean Ref No.
For any reference number called up l will require the relevent information along the top row in blue.
What l would like is to call up a Ref No and bring up the information which relates to this.
i.e Document Name etc.

I tried to setup a pivot table but got an error.

Regards
Justin

Reply | Quote

I am not sure what you are trying to do, I don’t see a document number, only a name. Could you give more details?

Some thoughts:
Individually you can use VLOOKUP to get the “ref No” given a “Document TItle”

To get a “title” given a “ref no” will require a combo of index and match. See post 244408

To make a table of “title” vs ref no” (assuming your current list has duplicates, could be done with a adv filter and extracting the unique items

To get summary info, you could use a pivot table.

Steve

Reply | Quote