| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 46584 | 2004-06-28 21:24:00 | Excel vlookup command | George Evans (5819) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 248228 | 2004-06-28 21:24:00 | I am trying to write a speadsheet in which I have a number different data tables (but all with same format) as named ranges. I want to be able to use the vlookup command to extract data and to set which of the tables to look up by using a formula to change the named range in the "table_array" field. Vlookup doesn't like my attempts to write a formula in the field. Can it be done? I can send a small sample spread sheet of the problem. |
George Evans (5819) | ||
| 248229 | 2004-06-28 23:55:00 | A friend found the solution for me with the "indirect" command. | George Evans (5819) | ||
| 248230 | 2004-06-29 00:00:00 | I do not think so. The table_array does not seem to recognise external names of arrays, only embedded ones. Thats in Excel 2002 anyway. Possibly a VBA routine could be a work around, but is outside my knowledge. |
godfather (25) | ||
| 248231 | 2004-06-29 00:00:00 | Just saw your reply. Must look at that myself. | godfather (25) | ||
| 248232 | 2004-06-29 01:46:00 | Hi, yes Indirect is the way to go. For example in cell B4 you have the word Sheet2 so you can use this to indicate cell A1 on sheet2 like this... =INDIRECT(B4&"!A1") You build your reference using "&" for the various components. Those parts in quotes are pure text and static while cell references (ie B4) grab the values in those cells. The only limitation with Indirect is you cant point to another workbook. |
parry (27) | ||
| 1 | |||||