Referencing a Cell in a Formula 1

[binaryfingers]

Hi

I have a workbook called test.xls and there are several sheets in it.

I have a summary page on another workbook and would like to reference the appropriate sheet that the cell name references

for example
currently the formula is
=VLOOKUP($A8,'[test.xls]MatsU'!$B$10:$IQ$100,4,false)

Instead of having the MatsU typed in, I would like it to reference the cell A5 on the summary sheet.

Cell A5 on the summary sheet is MatsU
Cell A6 on the summary shhet is MatsV

So the idea is that when I drag the formula across the worksheet, it references the appropriate sheet to the cell value

Hope this helps explain what I want to achieve...look forward to your help

 

[tsurikov]

Hi there,

I understand what you want to do, but I'm not sure it can be done the way you want it done. If the formula referenced only a CELL, you could easily use the INDIRECT function to assemble the cell reference. But that wouldn't work for a range of cells, which is what you want in your VLOOKUP function.

If you don't have too many sheets, could you use a nested IF statement? something like

=IF(A5="MatsU", VLOOKUP for sheet MatsU, IF(A5="MatsV", VLOOKUP for sheet MatsV, IF ... list all possible values in cell A5 and the corresponding VLOOKUPs for it ... ))

Beyond this - I defer to the other experts here
Good luck,
--Michael

 

[binaryfingers]

Hi Michael

I think I misled you with the multiple cell references, it was actually the Indirect function that I was looking for help with...

could you pass that solution over...Im sure it will be what I am looking for..

Thanks,

 

[Gavona]

Have a look at help, if still giving problems then post your formula here and someone will help. The format is:
=INDIRECT(ref_text,a1)
you will almost certainly want
=INDIRECT(ref_text,true)

Thanks,

Gavin

 

[BenRowe]

=VLOOKUP($A8,INDIRECT("[test.xls]"&A5&"$B$10:$IQ$100"),4,FALSE)
However, asw you drag it across the worksheet, A5 becomes B5, C5, etc. Therefore, you will need to have MatsU, MatsV, etc. entered in a row rather than column.

 

[binaryfingers]

Thanks BenRowe...