| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
I have 10 sheets with different tables on each.
Using If and Vlookup I've exceded my 7 references. What I would like is to be able to use the text data from a cell to point to one of the 10 sheets. i.e. so that the cell data is named the same as one of the sheets and Vlookup uses this in Table_array. So if the value of cell A2="Wire" Vlookup(C15,WireA1:C50,2) but if cell A3="Line" then Vlookup(C15,LineA1:C50,2) etc Many Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup
If none of your sheet names contains any space character:
=VLOOKUP(C15,INDIRECT(A2&"!A1:C50"),2) Or more generally: =VLOOKUP(C15,INDIRECT("'"&A2&"'!A1:C50"),2) (Note the apostrophe between the double quotes and another one before the exclamation mark). "Rolohound" wrote in message ... I have 10 sheets with different tables on each. Using If and Vlookup I've exceded my 7 references. What I would like is to be able to use the text data from a cell to point to one of the 10 sheets. i.e. so that the cell data is named the same as one of the sheets and Vlookup uses this in Table_array. So if the value of cell A2="Wire" Vlookup(C15,WireA1:C50,2) but if cell A3="Line" then Vlookup(C15,LineA1:C50,2) etc Many Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup
Try it like this:
=VLOOKUP(C15,INDIRECT("'"&A2&"'!A1:C50",2,0) Hope this helps. Pete On Dec 20, 1:40 pm, Rolohound wrote: I have 10 sheets with different tables on each. Using If and Vlookup I've exceded my 7 references. What I would like is to be able to use the text data from a cell to point to one of the 10 sheets. i.e. so that the cell data is named the same as one of the sheets and Vlookup uses this in Table_array. So if the value of cell A2="Wire" Vlookup(C15,WireA1:C50,2) but if cell A3="Line" then Vlookup(C15,LineA1:C50,2) etc Many Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup
Thankyou that worked exactly and should shorten the code considerably.
I've never used indirect before, but will look this function up as it seems to be a very useful tool within excel. Thanks once again. Rolohound "Stephen" wrote: If none of your sheet names contains any space character: =VLOOKUP(C15,INDIRECT(A2&"!A1:C50"),2) Or more generally: =VLOOKUP(C15,INDIRECT("'"&A2&"'!A1:C50"),2) (Note the apostrophe between the double quotes and another one before the exclamation mark). "Rolohound" wrote in message ... I have 10 sheets with different tables on each. Using If and Vlookup I've exceded my 7 references. What I would like is to be able to use the text data from a cell to point to one of the 10 sheets. i.e. so that the cell data is named the same as one of the sheets and Vlookup uses this in Table_array. So if the value of cell A2="Wire" Vlookup(C15,WireA1:C50,2) but if cell A3="Line" then Vlookup(C15,LineA1:C50,2) etc Many Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Vlookup
You may wish to look here for a good description of how to use INDIRECT:
http://www.cpearson.com/excel/indirect.htm "Rolohound" wrote in message ... Thankyou that worked exactly and should shorten the code considerably. I've never used indirect before, but will look this function up as it seems to be a very useful tool within excel. Thanks once again. Rolohound "Stephen" wrote: If none of your sheet names contains any space character: =VLOOKUP(C15,INDIRECT(A2&"!A1:C50"),2) Or more generally: =VLOOKUP(C15,INDIRECT("'"&A2&"'!A1:C50"),2) (Note the apostrophe between the double quotes and another one before the exclamation mark). "Rolohound" wrote in message ... I have 10 sheets with different tables on each. Using If and Vlookup I've exceded my 7 references. What I would like is to be able to use the text data from a cell to point to one of the 10 sheets. i.e. so that the cell data is named the same as one of the sheets and Vlookup uses this in Table_array. So if the value of cell A2="Wire" Vlookup(C15,WireA1:C50,2) but if cell A3="Line" then Vlookup(C15,LineA1:C50,2) etc Many Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
