Copying a finite cell (not finite cell psotition) as it moves around after various "sorts".

[Legacy 274910]

How can I copy the cell value in a cell (for example a value that may change from time to time as it is dependent on other data) to another cell, where the cells exist on different rows and rows that I will be sorting regularly in different ways?

(so the target cell and reference cell are both moving).

In other words, can each cell (not cell location) in excel be finitely referenced?

 

[Gary McMaster]

Name the cells. Please see Excel help for details.

Gary

 

[Legacy 274910]

Re: Copying a finite cell (not finite cell position) as it moves around after various "sorts".

Name the cells. Please see Excel help for details.

Gary

Hi, I have tried this and used the Excel help but unless there are further steps to take all this appears to do is (again) just copy the relative cell, not the specific one.

Any expansion on how to use "naming a cell" to copy the specific cell each time?

 

[Gary McMaster]

Re: Copying a finite cell (not finite cell position) as it moves around after various "sorts".

Assuming a cell is named "Test" (G10 for example)

Copy value of cell "G10" to cell "A1" with a formula in "A1"

Formula in A1:
=Test

Using VBA:

ActiveSheet.Range("A2").Value = ActiveSheet.Range("Test").Value

It should no longer matter what row or column the cell named "Test" is located in or how its position was changed.

Hope that helps.

Gary

 

[Legacy 274910]

Re: Copying a finite cell (not finite cell position) as it moves around after various "sorts".

Ok, I'm going to seem inexperienced here. I don't know much about vba or how to use it. Upon research, I don't have the "tools" drop-down as explained when I searched for help. Perhaps I have a basic version of excel?

 

[lsemmens]

Re: Copying a finite cell (not finite cell position) as it moves around after various "sorts".

It might help if you tell us which version of Excel you are using as M$ in their infinite wisdom has managed to "hide" some features that many of us take for granted in some of the more recent versions of Excel.

Do a search on "named ranges" in excel help which will take care of part of your problem, then you need replace the cell reference (i.e. A1 with the name)

 

[Legacy 274910]

Re: Copying a finite cell (not finite cell position) as it moves around after various "sorts".

OK, I have found the named-ranges section, I have read excel help all about named ranges and I'm still stuck on the fundamental problem that while I can name a cell (eg. "BOB"), I can make another cell (not named) = BOB, whenever I sort the sheet, the cell with no name reads some other cell and not BOB.

I'll play around more, perhaps name both cells?

 

[Legacy 274910]

Re: Copying a finite cell (not finite cell position) as it moves around after various "sorts".

Naming both cells did nothing helpful.

I must be not doing enough because all I am doing is referring to a named cell that after a sort moves but whose name remains in the intial position.

If I name the cell in A20 and call it BOTTOM which contains some data I shall refer to as LEGS, after I have sorted my sheet, the LEGS move but the BOTTOM remains in the same place.

This means any cel referencing BOTTOM simply references the cell in A20.

 

[Gary McMaster]

Re: Copying a finite cell (not finite cell position) as it moves around after various "sorts".

This means any cel referencing BOTTOM simply references the cell in A20

Yes, that is how naming works and it obviously is not solving your problem. Sorry for misinterpreting your question and leading you to believe you were doing something wrong.

I usually find "custom autofilters" more useful than sorting to find data I need. Have you tried that?

Gary

 

[Legacy 274910]

Re: Copying a finite cell (not finite cell position) as it moves around after various "sorts".

I will, of course look into "custom autofilters" for a way around my issue. My question remains though, is Excel really missing such a simple trick as being able to reference specific data even though it moves (in terms of cell reference, but ot in value or meaning) upon sorting?

PS. Why on this forum is it so painfully difficult to type? Some kind of "live updating/autosave"? Below I will type the same PS without correcting.
PS. Wy on thsforum s t o pinfully dfficult t type?Sme in o "lie updig/atsave"? Beow I wil tpe the same S ithout corcting.