Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Display contents of a specific cell even if a group of cells is mo
In one cell, I want to display the contents of another cell which is part of
a range of cells which contain a list that is constantly being rearranged (resorted, draged, new data inserted, etc.). Everything I've tried (including absolute references) results in the display cell pointing to the same data as the range of cells changes. Thanks! |
#2
|
|||
|
|||
Use INDIRECT
=INDIRECT("'Sheet2'!A1") will always return what's in A1 in Sheet2 -- Regards, Peo Sjoblom "Larry Heine" wrote in message ... In one cell, I want to display the contents of another cell which is part of a range of cells which contain a list that is constantly being rearranged (resorted, draged, new data inserted, etc.). Everything I've tried (including absolute references) results in the display cell pointing to the same data as the range of cells changes. Thanks! |
#3
|
|||
|
|||
Do you mean refer to say A20 even if A20 is dragged away, your reference
continues to look at A20? =INDIRECT("A20") -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Larry Heine" wrote in message ... In one cell, I want to display the contents of another cell which is part of a range of cells which contain a list that is constantly being rearranged (resorted, draged, new data inserted, etc.). Everything I've tried (including absolute references) results in the display cell pointing to the same data as the range of cells changes. Thanks! |
#4
|
|||
|
|||
You could try the following:
Suppose your target cell is C5. I'm assuming that you always want C5, not where the data in C5 may move to. Try using offset(A1,4,2) The 4 is how many rows away C5 is from A1. The 2 is how many columns away C5 is from A1. Art "Larry Heine" wrote: In one cell, I want to display the contents of another cell which is part of a range of cells which contain a list that is constantly being rearranged (resorted, draged, new data inserted, etc.). Everything I've tried (including absolute references) results in the display cell pointing to the same data as the range of cells changes. Thanks! |
#5
|
|||
|
|||
Thanks, Peo, two nagging questions answered in 20 minutes!
"Peo Sjoblom" wrote: Use INDIRECT =INDIRECT("'Sheet2'!A1") will always return what's in A1 in Sheet2 -- Regards, Peo Sjoblom "Larry Heine" wrote in message ... In one cell, I want to display the contents of another cell which is part of a range of cells which contain a list that is constantly being rearranged (resorted, draged, new data inserted, etc.). Everything I've tried (including absolute references) results in the display cell pointing to the same data as the range of cells changes. Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display contents of a specific cell even if a group of cells i
What if you want to follow the contents of cell C5 if it moves to a new
position due to a sort?? "Art" wrote: You could try the following: Suppose your target cell is C5. I'm assuming that you always want C5, not where the data in C5 may move to. Try using offset(A1,4,2) The 4 is how many rows away C5 is from A1. The 2 is how many columns away C5 is from A1. Art "Larry Heine" wrote: In one cell, I want to display the contents of another cell which is part of a range of cells which contain a list that is constantly being rearranged (resorted, draged, new data inserted, etc.). Everything I've tried (including absolute references) results in the display cell pointing to the same data as the range of cells changes. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display actual contents of cell | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions | |||
copy group of cells to another group of cells using "IF" in third | Excel Worksheet Functions | |||
Modify Row & Cell Contents based upon Cells Values | Excel Worksheet Functions | |||
Modify Row & Cell Contents based upon Cells Values | Excel Worksheet Functions |