Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
transferring formulas with absolute reference to multiple tabs
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? -- Tim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
transferring formulas with absolute reference to multiple tabs
Maybe, if you give an example, soemone might understand what your problem is?
"talderman" wrote: 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? -- Tim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
transferring formulas with absolute reference to multiple tabs
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 Maybe, if you give an example, soemone might understand what your problem is? "talderman" wrote: 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? -- Tim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
transferring formulas with absolute reference to multiple tabs
)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. "talderman" wrote: 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 Maybe, if you give an example, soemone might understand what your problem is? "talderman" wrote: 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? -- Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel should let me format worksheet tabs in multiple rows. | Excel Worksheet Functions | |||
Trouble with making a 3D reference absolute | Excel Discussion (Misc queries) | |||
Absolute Worksheet reference number | Excel Discussion (Misc queries) | |||
Unmovable absolute reference | Excel Worksheet Functions | |||
How do I reference multiple rows | Excel Worksheet Functions |