transferring formulas with absolute reference to multiple tabs

[talderman]

The formula that is giving me trouble is VLOOKUP. I need to use absolute
references for the table array so that if the table or the cell that has the
VLOOKUP is moved the cells automatically update. The problem occurs when I
need to copy this information from one tab to another. The table array has
absolute references (for the column and the row) so the cells do not update
or change to some new specified cells on the new tab. I still need the cells
to be absolute references but I would like for them to update to the cell in
the new tab. Can I have my cake and eat it?

 

[kassie]

Maybe, if you give an example, soemone might understand what your problem is?

 

[talderman]

The way the formula looks goes as follows:

VLOOKUP(D1,$A$1:$B$3,2,FALSE) This formula is in cells E1,E2,E3 in Column E.
The cells output BOB(E1), JOE(E2), BOB(E3)

A B C D E
1 1 BOB 1 BOB
2 2 TOM 3 JOE
3 3 JOE 1 BOB
4
5

A1:B3 TABLE ARRAY
Column A corresponds to Column B
1 corresponds to Bob
2 corresponds to Tom
3 corresponds to Joe

The cell D1 has the number 1 in it. The formula located in cell E1 reads
the value 1 and outputs BOB because in the table array 1 corresponds to BOB.
E2 reads the value 3 located in D2 and outputs JOE in cell E2. Long story
longer.

The table array needs to have an absolute reference that is why the $ signs
are located in the formula. The problem is I would like to copy cells A1:E3
and put them on a different tab or even into a different drawing. The
absolute reference signs hard code the formula to read $A$1:$B$3 for the
table array in the new tab or drawing even though the table isn't located in
these cells. It wouldn't be practical to change the table array for every
cell in the new tab or drawing.

Sorry I deleted some text by accident in this posting

 

[kassie]

)As I understand it, you want to move your data (A1:B3 to another sheet, and
have your formula update to reference the new location? Select your table
(A1:B3), press <Ctrl><X>, go to A1 on Sheet 2 and press <Ctrl><V>. It will
change to read: =VLOOKUP(D1,Sheet2!$A$1:$B$3,2,FALSE). If you want to place
it in a different location on Sheet 2 (iow not in A1:B3), you click in the
area you want your table to start, and then press <Ctrl><V>. it will
automatically change your formula to read eg
=VLOOKUP(D1,Sheet2!$E$8:$F$10,2,FALSE). This in spite of the fact that your
formula contains absolute references.