I am trying to copy a vlookup formula to a whole column of cells with out the
formula changing the "table_array" part of the formula. When the formula is
copyed excell automaticaly changes the "lookup_value" to match the row number
that the cell is copied to. I want it to continue to do that without changing
the "table_array" part of the formula - Thanks in advance

1. Select the cell with the vlookup formula that you want to copy.
2. Click on the cell reference in the "table_array" part of the formula (the part that specifies the range of cells to search for the lookup value).
3. Press the F4 key on your keyboard. This will add dollar signs ($) to the cell reference, making it an absolute reference. For example, if the original formula had "A1:B10" as the table array, it will now have "$A$1:$B$10".
4. Copy the formula by pressing Ctrl+C or right-clicking and selecting "Copy".
5. Select the range of cells where you want to paste the formula.
6. Paste the formula by pressing Ctrl+V or right-clicking and selecting "Paste".
7. Excel will automatically adjust the "lookup_value" part of the formula to match the row number of each cell in the range you pasted to, but the "table_array" part will remain the same.

By using absolute cell references, you can copy the formula to multiple cells without worrying about the "table_array" part of the formula changing.

Hi there,

make the Table array's "absolute" (e.g. $c$1:$F8). Tip: Position your cursor
marker on e.g. C1 and press the F4 key, that' gives you $c$1 automatically)

Thomas

"not 2 excell" wrote:

I am trying to copy a vlookup formula to a whole column of cells with out the
formula changing the "table_array" part of the formula. When the formula is
copyed excell automaticaly changes the "lookup_value" to match the row number
that the cell is copied to. I want it to continue to do that without changing
the "table_array" part of the formula - Thanks in advance

Thomas,

Sounds good to me however I am not very excell savy. when I hit F4 it opend
a new workbook?? (I know it's me not you) the formula that I am using is
=VLOOKUP(A4,'PSI Data '!=VLOOKUP(A4,'PSI Data '!A2:Z2000,12,FALSE),12,FALSE).
I need the A2:Z2000 part to stay the same in each copied cell

"Thomas" wrote:

Hi there,

make the Table array's "absolute" (e.g. $c$1:$F8). Tip: Position your cursor
marker on e.g. C1 and press the F4 key, that' gives you $c$1 automatically)

Thomas

"not 2 excell" wrote:

I am trying to copy a vlookup formula to a whole column of cells with out the
formula changing the "table_array" part of the formula. When the formula is
copyed excell automaticaly changes the "lookup_value" to match the row number
that the cell is copied to. I want it to continue to do that without changing
the "table_array" part of the formula - Thanks in advance

Thomas,

Ok !!! and thanks to you - By placing the $ sign in front of the
"table_array" reference cells and columns it works. Thanks again


"Thomas" wrote:

Hi there,

make the Table array's "absolute" (e.g. $c$1:$F8). Tip: Position your cursor
marker on e.g. C1 and press the F4 key, that' gives you $c$1 automatically)

Thomas

"not 2 excell" wrote:

I am trying to copy a vlookup formula to a whole column of cells with out the
formula changing the "table_array" part of the formula. When the formula is
copyed excell automaticaly changes the "lookup_value" to match the row number
that the cell is copied to. I want it to continue to do that without changing
the "table_array" part of the formula - Thanks in advance

not 2 excell,

What Thomas was talking about was when you are editing the formula.

If you go to a cell that already has a formula in it, hit [F2] (or
double-click the cell or click in the formula bar) to edit the formula. Now
position your cursor on a cell reference (for example: "=C4+D4" place the
cursor on C4 (between the = and the C, between the C and the 4, or between
the 4 and the +)). Now if you press [F4], it will change "C4" to "$C$4".
Press it again and you will get "C$4". Another press will get you "$C4".
One more press will get you back to "C4". Keep pressing it and it will
cycle through all of the possibilities of absolute and relative.

When you are editing a cell/formula, the [F4] key will cycle through
absolute/relative of a reference. But, if you are not editing a cell, [F4]
is Redo: it will repeat the last thing you did; or if you have just undone
a bunch of things in your file ([Ctrl] + Z or the Undo button on the
toolbar), each press of the [F4] key will Redo the next thing in the Redo
list. [F4] is equivalent to [Ctrl] + Y or clicking the Redo button on the
toolbar if you have undone some things.

I hope this helps,

Conan




"not 2 excell" wrote in message
...
Thomas,

Sounds good to me however I am not very excell savy. when I hit F4 it
opend
a new workbook?? (I know it's me not you) the formula that I am using is
=VLOOKUP(A4,'PSI Data '!=VLOOKUP(A4,'PSI Data
'!A2:Z2000,12,FALSE),12,FALSE).
I need the A2:Z2000 part to stay the same in each copied cell

"Thomas" wrote:

Hi there,

make the Table array's "absolute" (e.g. $c$1:$F8). Tip: Position your
cursor
marker on e.g. C1 and press the F4 key, that' gives you $c$1
automatically)

Thomas

"not 2 excell" wrote:

I am trying to copy a vlookup formula to a whole column of cells with
out the
formula changing the "table_array" part of the formula. When the
formula is
copyed excell automaticaly changes the "lookup_value" to match the row
number
that the cell is copied to. I want it to continue to do that without
changing
the "table_array" part of the formula - Thanks in advance