Vlookup Formula Issue

[Chuck85]

Hi everyone thanks in advance for any help.

I'm having an issue with VLOOKUP, I've had to use multiple VLOOKUPS all with different table array references linking to separate documents. The issue I'm having is that in the VLOOKUP formula if I copy and paste the table array reference in, the vlookup works as expected. Because I'm trying to automate lots of fields, if I press on the cell with the required table array reference I get the cell number e.g A2 in the formula instead of the complete table array reference, this always comes back as an error.

Example

A1= Rob (Both these names have separate spreadsheets each) B1=C:\names\.....Rob!namesA1:E6... (Table Array location example of the separate spreadsheets)
A2= Dave B2=C:\names\.....Dave!namesA1:E6....

When I copy and paste B1 I will have:

=VLOOKUP(A1,C:\names\.....Rob!namesA1:E6...,1,FALSE) which works fine.

When I don't copy and paste B1 and simply press on the cell required I get.

=VLOOKUP(A1,B1,1,FALSE) This always gives an error. Need this to work though as I have to copy this formula for many different names with there own table array references.

is there anything I can do to either copy over the text instead of cell value?

Thanks for your time

[bebo021999]

Use INDIRECT(B1) to interprete text in B1 to address instead.

[Chuck85]

Thanks for the reply, tried INDIRECT still getting #ref.