Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old February 3rd 05, 07:39 PM
Larry Heine
 
Posts: n/a
Default 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   Report Post  
Old February 3rd 05, 07:47 PM
Peo Sjoblom
 
Posts: n/a
Default

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   Report Post  
Old February 3rd 05, 07:49 PM
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Old February 3rd 05, 07:51 PM
Art
 
Posts: n/a
Default

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   Report Post  
Old February 3rd 05, 08:01 PM
Larry Heine
 
Posts: n/a
Default

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   Report Post  
Old December 4th 08, 10:33 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 17
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Display actual contents of cell xmasbob Excel Discussion (Misc queries) 1 December 6th 04 05:09 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 07:16 PM
copy group of cells to another group of cells using "IF" in third Chuckak Excel Worksheet Functions 2 November 10th 04 06:04 PM
Modify Row & Cell Contents based upon Cells Values bpat1434 Excel Worksheet Functions 0 November 7th 04 03:31 PM
Modify Row & Cell Contents based upon Cells Values bpat1434 Excel Worksheet Functions 1 November 7th 04 12:43 PM


All times are GMT +1. The time now is 12:36 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017