I need to copy a vlookup formula down a specific column. However, the
lookup_value needs to change from row to row when I copy the formula since it
references the contents of the cells in column A. Also the contents of the
cells in column A are text not numbers therefore the INDIRECT function is not
working for me.

Example
A B
1 ROAA010A VLOOKUP("ROAA010A",'[Allocation Data.xls]Sheet1...
2 ROAB070A VLOOKUP("ROAB070A",'[Allocation Data.xls]Sheet1...
3 ROAM050A VLOOKUP("ROAM050A",'[Allocation Data.xls]Sheet1...
.. . .
.. . .
.. . .

Any help will be greatly appreciated.

Thanks in advance

How about

VLOOKUP(A1,'[Allocation Data.xls]Sheet1...


--

HTH

RP
(remove nothere from the email address if mailing direct)


"EMarre" wrote in message
...
I need to copy a vlookup formula down a specific column. However, the
lookup_value needs to change from row to row when I copy the formula since
it
references the contents of the cells in column A. Also the contents of the
cells in column A are text not numbers therefore the INDIRECT function is
not
working for me.

Example
A B
1 ROAA010A VLOOKUP("ROAA010A",'[Allocation Data.xls]Sheet1...
2 ROAB070A VLOOKUP("ROAB070A",'[Allocation Data.xls]Sheet1...
3 ROAM050A VLOOKUP("ROAM050A",'[Allocation Data.xls]Sheet1...
. . .
. . .
. . .

Any help will be greatly appreciated.

Thanks in advance

The formula in B1 would be VLOOKUP(A1, etc). Since you're going to
copy this down a column, here's a tip: when you define the look-up
range in the VLOOKUP use reference anchors (for instance, $D$1:$E$10)
to lock the range.

Dave O,

Thanks for your help. It works !

"Dave O" wrote:

The formula in B1 would be VLOOKUP(A1, etc). Since you're going to
copy this down a column, here's a tip: when you define the look-up
range in the VLOOKUP use reference anchors (for instance, $D$1:$E$10)
to lock the range.

Bob,

Thanks for your help. It works! I think I got really confused with the
formula while I was writing it


"Bob Phillips" wrote:


How about

VLOOKUP(A1,'[Allocation Data.xls]Sheet1...


--

HTH

RP
(remove nothere from the email address if mailing direct)


"EMarre" wrote in message
...
I need to copy a vlookup formula down a specific column. However, the
lookup_value needs to change from row to row when I copy the formula since
it
references the contents of the cells in column A. Also the contents of the
cells in column A are text not numbers therefore the INDIRECT function is
not
working for me.

Example
A B
1 ROAA010A VLOOKUP("ROAA010A",'[Allocation Data.xls]Sheet1...
2 ROAB070A VLOOKUP("ROAB070A",'[Allocation Data.xls]Sheet1...
3 ROAM050A VLOOKUP("ROAM050A",'[Allocation Data.xls]Sheet1...
. . .
. . .
. . .

Any help will be greatly appreciated.

Thanks in advance