"truly absolute references" to cells using X/Y coordinates

[boarders paradise]

Hi everyone.

My scenario is a very common one.

Please Login or Register  to view this content.

I want the formulas one sheet 1 to have absolute references to the data cells on sheet 2 … so that a formula referencing 'data'!A1 will *always* reference the top left cell on sheet 2, no matter what happens on sheet 2 (cutting, pasting, deleting, inserting rows, etc.). This is what I will call henceforth "truly absolute reference" in this thread.

One of the rationales behind this approach is that you can lock the results sheet (even with a password) and hand the whole workbook to co-workers who fill in the data sheet or data sheets. Because otherwise they would inevitably screw up your formulas on sheet1, creating #REF! errors, etc. when they cut/paste/insert_rows/delete_rows on sheet2.

Many users find themselves in exactly the same scenario, by way of example:

I'm using Excel 2003 for payroll deductions. I have one workbook with two worksheets. One worksheet (information sheet) is were I enter all of the information. The other (payroll sheet) calculates the information from the first. I want to use an indirect formula so if a co-worker goes into the information sheet and cuts and pastes something, it won't change the formula on the payroll deduction sheet.

I have posted an example .XLS file as attachment. But please bear in mind that it is intended as a way of exemplification only, while the solutions should apply to the general *concept* (calculations on sheet 1 with truly absolute references to the data on sheet 2).

In this simplified example of one of my personal projects, I have a list of audio files on two data sheets together with some attributes (artist, album title). The main (calculations) sheet compares the data sheets and checks if the attributes are identical or not. There is a column labeled "sync" which checks if the file lists on all 3 sheets are in sync. It returns "ok" if e.g. in row 7 of all sheets the same audio file is listed.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The general question is: HOW CAN I CREATE TRULY ABSOLUTE REFERENCES ?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The problem of the answer not being obvious lies in Excel's misleading terminology. (To explain why, I will use 2 expressions: "referrer" and "reference" with "referrer" being the formula which references (=refers to) the reference ... and with "reference" being the cell referenced by the referrer.) What Excel calls "absolute reference" is actually only just a copy protection for the referrer and has nothing to do whatsoever with a truly absolute reference which will always point to a cell with given X/Y coordinates. To corroborate this I quote Chip Pearson at Pearson Software Consulting:

Even with an absolute referencing style, Excel will still change row and column references when you insert a row or column. To have a truly absolute cell reference that will not change under any circumstances, use the INDIRECT function. For example =INDIRECT("A1") will always refer to cell A1, regardless of any changes made to the worksheet.

I am not saying the dollar symbol is useless the way it is implemented. But Excel should have called it "copy protection" or something along those lines. What Excel apparently failed to implement is a truly absolute reference, always referring to a specific cell (with X/Y coordinates). This is what should be called "absolute reference" and should have been implemented in a way just as user-friendly as the dollar sign. Why they didn't just pick a different symbol, e.g. the pagraph sign is beyond me. This is how I would have designed it:

Please Login or Register  to view this content.

This is the reason why others and I myself have been trying to create a list of truly absolute references

§A§1
§A§2
§A§3
§A§4
§A§5
§A§6
[...]
§A§100

So what is the bottom line? Is it true, that truly absolute references simply do not exist Excel? Is the user forced to workaround this indirectly by way of the INDIRECT function (which is basically just fooling Excel into not updating a reference because it is hidden inside a string)? Is there no other way? If so, that seems to be a http://de.wikipedia.org/wiki/Schildbürger type of scenario.

[romperstomper]

Is it true, that truly absolute references simply do not exist Excel? Is the user forced to workaround this indirectly by way of the INDIRECT function (which is basically just fooling Excel into not updating a reference because it is hidden inside a string)?

Yes, it is true.

[boarders paradise]

Yes, it is true.

thank you for your reply. Does that mean that there is no other way than wrapping INDIRECT("......") around each cell name, thus bloating a formula like

=IF(AND('sheet'!B2="";'sheet2'!B2="");".";IF(AND('sheet'!B2="";'sheet2'!B2<>"");"only sheet2";IF(AND('sheet'!B2<>"";'sheet2'!B2="");"!only sheet";IF(IDENTICAL('sheet'!B2;'sheet2'!B2);"=";IF(IDENTICAL('sheet'!B2;LEFT('sheet2'!B2;30));"=trunc";IF('sheet'!B2=LEFT('sheet2'!B2;30);"!caps.diff";"!DIFF"))))))

... by almost 100% ??

=IF(AND(INDIRECT("'sheet'!B2")="";INDIRECT("'sheet2'!B2")="");".";IF(AND(INDIRECT("'sheet'!B2")="";INDIRECT("'sheet2'!B2")<>"");"only v2";IF(AND(INDIRECT("'sheet'!B2")<>"";INDIRECT("'sheet2'!B2")="");"!only v1";IF(IDENTICAL(INDIRECT("'sheet'!B2");INDIRECT("'sheet2'!B2"));"=";IF(IDENTICAL(INDIRECT("'sheet'!B2");LEFT(INDIRECT("'sheet2'!B2");30));"=trunc";IF(INDIRECT("'sheet'!B2")=LEFT(INDIRECT("'sheet2'!B2");30);"!caps.diff";"!DIFF"))))))

[romperstomper]

No, nothing 100% foolproof. (and if you did that you'd be mad).
You could use a formula like =INDEX(Sheet1!1:1048576,1,1) which should work as long as they don't delete all the rows on a sheet, but that would still be nuts.
Far better to prevent your users from deleting anything, or give them just a data sheet to fill out and then produce the reports afterwards. Or even, train them not to be such muppets.

[boarders paradise]

point taken.

but substituting 'Sheet2'!A1 with INDEX('Sheet2'!1:1048576,1,1) is even more complicated then substituting 'Sheet2'!A1 with INDIRECT("'Sheet2'!A1"), which already is tricky for longer formulas (see post #3)

... plus that is for ONE SINGLE CELL only, whereas in my workbook I have 17 columns and a couple of thousands rows, equalling tens of thousands of cells ... searching for all variables and wrapping your INDEX(...) function around is not humanly possible.

While your advice "train your co-workers not to be such muppets" might sometimes be unrealistic in itself, there are cases where even perfectly trained workers WILL change the formulas on sheet 1, for example when they realize they have to delete a row in their data on sheet 2. That's a perfectly legitimate operation and there must be a way for Excel to handle such cases.

And even if we exclude co-workers ... what about myself? Let's go back to the example XLS file I posted in OP. Open it and you will see that it is designed to paste data on the two "data sheets", which will be analyzed by the "results sheet" spitting out the final results. But as soon as you paste any data on the "data sheets", the "results sheet" is filled with #REF! errors everywhere. What to do?

[romperstomper]

Can't open the file as I'm on a phone, but that should only occur if you cut and paste.

As a general rule I can't see why you would have a one to one link with a data table. If you are using summary formulas then you can use dynamic named ranges (or tables in later versions of excel). If they are setup properly, there shouldn't be a problem unless the users do something really daft.

[martindwilson]

INDEX('Sheet2'!1:1048576,1,1) can just be
INDEX('Sheet2'!a:a,1)

=IF(AND(index(‘sheet’!B:b,2)="";index(‘sheet2’!B:b,2)="");".";IF(AND(index(‘sheet’!B:b,2)="";index(‘sheet2’!B:b,2)<>"");"only sheet2";IF(AND(index(‘sheet’!B:b,2)<>"";index(‘sheet2’!B:b,2)="");"!only sheet";IF(IDENTICAL(index(‘sheet’!B:b,2);index(‘sheet2’!B:b,2));"=";IF(IDENTICAL(index(‘sheet’!B:b,2);LEFT(index(‘sheet2’!B:b,2);30));"=trun c";IF(index(‘sheet’!B:b,2)=LEFT(index(‘sheet2’!B:b,2);30);"!caps.diff";"!DIFF"))))))

[romperstomper]

No, because that would break if you deleted column A.

[boarders paradise]

... should only occur if you cut and paste.

I do need pasting to import the data (onto the data sheets). That's the whole purpose of the XLS file.

I'm on a phone

these are the columns from the XLS file (see screenshot below):

sheet1 ("results sheet"): sync check .... file .... artist check .... album check .... title check
sheet2 ("data sheet"): file .... artist .... album .... title
sheet3 ("data sheet"): file .... artist .... album .... title

"sync" checks if all 3 sheets are aligned properly, i.e. if e.g. the "file" in row 7 is the same on all three sheets.
Then the artist, album and title columns on sheet 1 check if artist, album and title are the same on sheet 2 and 3.

(the real project is much more complicated, but that's an oversimplified version of it).

[martindwilson]

good point but ,now if people are deleting columns,id sack them!
but in excel 2002> cant you protect the sheet allowing everything except Insert columns and or Delete columns?

[boarders paradise]

if people are deleting columns,id sack them! [...] cant you protect the sheet allowing everything except Insert columns and or Delete columns?

that seems to be besides the point. As said above (#5 and #9), I don't see a solution even if I am working alone. Pasting data onto sheet 2 also breaks sheet 1. (ditto for deleting e.g. row 7 on both data sheets (sheet 2+ sheet 3))

[romperstomper]

Let me put that another way: that should only happen if you CUT and paste, not if you copy and paste.

[boarders paradise]

You are right, I also realized that in the meanwhile.
How come? What's the difference for Excel?

Paste is Paste, regardless of whether the source data (which is in a complete different file in my case) is deleted!
There shouldn't be any difference.
The fact that there is, seems to reveal illogical program behaviour.

[martindwilson]

why not just accept the limitations and work with what does work. no one here afik wrote the source code for excel. we are here to help not defend or otherwise excel.

[romperstomper]

If you cut and paste, you move the old cell to the new location and effectively destroy the existing one (hence the REF error). If you copy and paste, the destination cell remains but takes on the properties of the source cell. This also means that any formulas pointing to the source cell will update if you cut and won't if you copy.

[teylyn]

boardersparadise,

this is in effect a duplicate question, since you asked the same in a previous thread and then abandoned it when people asked for the reason you need those absolute references.

It would have been polite to at least acknowledge the efforts provided in your other thread and point to the new question.

[boarders paradise]

[...] in a previous thread and then abandoned it when people asked for the reason you need those absolute references. It would have been polite to at least acknowledge the efforts provided in your other thread and point to the new question.

(1) I did not abandon it! Check the edit to my last post in the thread I alledgedly abandoned (it is too long to re-post here).

(2) PLUS see the edit I had made in the OP of said thread.

(3) You also missed my acknowldgements in the OP(!) above (in this thread here), where I was explicitely linking to the old thread:

This is the reason why others and I myself have been trying to create a list of truly absolute references

I decided to re-start from scratch in a new thread because the old thread was convoluted, headed in the wrong direction, based on misconceptions and dealing with a method/function which turned out to be the wrong one for my scenario any way.

I could have added an additional post to the long, old thread, but I decided on purpose to mark the thread as solved and to edit my last posting instead - as this is best practices - because a re-post is considered a "bump" as it jumps back to position #1 in the forum (egoistic behaviour). This was not necessary, as it was solved.

It is not my fault if you did miss all 3(!) of my acknowledgements and cross-references mentioned above, so I would kindly ask you to take back the infraction/warning you sent me via PM and then delete this (my) posting and yours, as they both are off-topic here and detract other readers form the this thread's issue.

.... in fact:
Since you had been so forthcoming and helping in the other thread, I was about to contact you via PM, and ask if you could continue to help me here, but I decided against it, because I did not know if you would consider it harrassment (there are lots of forum moderators on other borads who have a sig saying "don't contact me via PM or IM for support or you will be banned" ... and that's very understandably so, because other readers should benefit from the solutions and answers, too!)

You are more than welcome if you want to help me and contribute something positive here, too.

[boarders paradise]

why not just accept

because I also want to *understand*.

and work with what does work.

I do try

we are not here to defend excel.

you obviously don't need to and I wouldn't see why you feel you would have to.

we are here to help

and I am indeed more grateful for that, than I could ever say!
(although I do always say it, check my threads)

[martindwilson]

the thing is excel doesn't offer the facility you want, so that's it really, unless microsoft change the way its written . open office calc works the same way and i assume but i haven't checked that google docs does the same thing

[boarders paradise]

many thanks for your explanation.

If you copy and paste, the destination cell remains but takes on the properties of the source cell.

If you cut and paste, the destination cell also takes on the properties of the source cell, no?

If you cut and paste, you move the old cell to the new location and effectively destroy the existing one (hence the REF error). This also means that any formulas pointing to the source cell will update if you cut and won't if you copy.

OK, I get the second part (second sentence). But I DON'T have any formulas pointing to the source cell. I only have formulas pointing to the destination cell. And it shouldn't matter how the content gets there, i.e. whether it is
- pasted there after copying
- pasted there after cutting
- or typed in there manually

In my case I have a file A.xls with 3 sheets. Sheet 1 (still in file A !) points to a cell in sheet 2 (still in file A !).
Now I open a file B.tab (tab separated values) and cut or copy a cell and paste it into sheet 2 of file A. The cell in sheet 2 now has a different value. But why would sheet 1 have a #REF! error now?
I can see, why there would be a #REF! error if I had anything pointing to a source cell discarded due to a cut+paste action. But I don't have any such reference to a disappearing source cell.

[martindwilson]

it does the same thing even within the same sheet
i think that the action of cut/paste actually deletes the cell being pasted into and recreates it but the formula cant cope with that
interestingly open office can cope , cut paste does not produce ref errors
i dont think excel uses office clipboard at this point, because if you cut/paste into note pad then cut paste back into excel it updates just fine

[boarders paradise]

it does the same thing even within the same sheet

But my point was that I just quickly opened a completely different file, with NO REFERENCE whatsoever to that file, pasted the info from there and closed it again immediately.

looking at the definitions (and forgive me for going back to the basics):

copy+paste:
(1) when you copy (CTRL+C) you copy source content to the clipboard, duplicating it there.
(2) When you subsequently paste (CTRL+V), the clipboard content is copied to the paste destination.
(3) the source is left untouched and unaltered.

cut+paste:
(1) when you cut (CTRL+X) you *copy* source content to the clipboard, duplicating it there.
(2) When you subsequently paste (CTRL+V), the clipboard content is copied to the paste destination
(3) AND the source content is simultaneously deleted.

The only time, when something is disappearing which could have been referenced to is in the 3rd step of the cut+paste operation: namely disappearing source content.

The point is: I never had any reference to the source content!

[boarders paradise]

I have written my previous answer before you updated your posting with

interestingly open office can cope, cut paste does not produce ref errors [...]
if you cut/paste into note pad then cut paste back into excel it updates just fine

that explains it all.

However, I do want to stick with Excel, so I guess the bottom line here is that I will have to accept, that this behaviour is somehow inconsistent and illogical and I will just have to remember to deal with that fact (by using copy+paste instead of cut+paste).

[boarders paradise]

i dont think excel uses office clipboard at this point

I DO think it does. I went to view > task pane > clipboard and as soon as you click CTRL+C or CTRL+X, in both cases the content appears in the clipboard.

[martindwilson]

thats the way it works in excel just put =b1 in a1 .put xxxx in c1 now cut /paste c1 into b1 ,a1 gives ref error ,
now b1 didn't have any formulas in it nor did the c1 , but the action of cut/paste within excel and i presume excel knows this is happening and the paste is not whats on the clipboard but some other mechanism within in itself probably its trying to remember lots of things about what it actually copied that are irrelevant to whats actually on the clipboard it then seems to temporarily destroy b1 so it doesn't exist and a1 gives a ref error
but of course the cell is rebuilt as b1 its too late for a1
note on clipboard
clipboard seems to hold just the cell value so if a1=b1 and b1 had xxxx in it copying/cutting a1 would store xxxx on the clip board but within excel it would also hold the underlying formula.i think!

[boarders paradise]

thats the way it works in excel just put =b1 in a1 .put xxxx in c1 now cut /paste c1 into b1 ,a1 gives ref error

a very demonstrative example!

I think there are good chances you nailed it where the illogical behaviour is coming from.

it then seems to temporarily destroy b1 so it doesn't exist

ok, but b1 had never even existed in the first place, it was empty (and still there was no #REF! error). you said it yourself:

now b1 didn't have any formulas in it

Anyway, the program behaviour is illogical so this might be the reason for it nevertheless.

[boarders paradise]

Aside from all that guessing what might be happening behind the scenes:

Let's not forget that with a #REF! error, a cell is saying
"I don't know where I am referring to (= what I am referencing) !".

In a consistent, logical world this can only happen, when the reference target ceases to exist.
This is nowhere the case here in this thread, neither in my own scenario (OP) nor in the example in post#25.

[boarders paradise]

thats the way it works in excel just put =b1 in a1 .put xxxx in c1 now cut /paste c1 into b1 ,a1 gives ref error

Think of it like you are in your favourite restaurant. You (whoever reads this post) are A1. The toilets (B1) are currently empty(B1 is empty cell). You know where the toilets are (put "=B1" in A1). Now someone ("xxxx") who had been at one of the other restaurant tables (C1) enters the toilets (cut/paste C1 into B1). They are now occupied (with "xxxx"). You (A1) might not know where he ("xxxx") came from, but you STILL know what you knew in the first place, namely WHERE the toilets are ("=B1"). (hence why there shouldn't be a #REF! error)

[shg]

Excel works the way it works. Barking at the Moon won't change it.

[boarders paradise]

it's called humour.

And what you mistook for barking was a discussion between martindwilson, romperstomper and me, aiming at establishing whether or not Excel's behaviour with regards to cut+paste/copy+paste is logical.

[boarders paradise]

Let me put that another way: that should only happen if you CUT and paste, not if you copy and paste.

anyway, thank you for your tip. This helps working around the cut+paste bug.

ok, I guess the bottom line of this issue is that the only way (or does anybody know other methods??) the Excel user can create truly absolute references consists in using the INDEX( ) or the INDIRECT( ) functions. This approach is formula-bloating beyond readability (see #3), plus - what's worse - it won't auto-fill cells when copying them all the way down, so the user can manually create 1 or 2 - at best a few - truly absolute references from the calculations sheet to the data sheet(s), but it is impossible for the user to fill an entire calculations sheet with truly absolute references (to the data sheets) only.

Given that a calculations sheet with truly absolute references only, seems to be an impossible thing (or did I miss something?), the only remaining approach is settling for relative references, forcing the user to work around disappearing references (the VERY reason for not wanting relative references in the first place but trying to create truly absolute ones), for example using copy+pate to work around the cut+paste bug.

However other operations - like deleting rows on the data sheets - will still inevitably lead to #REF! errors on the calculations sheet. And there doesn't seem to be a way around it. Or at least it has not been laid out in this thread so far. This also means, that the whole "co-worker scenario" detailed in OP -- creating a calculations sheet and let co-workers freely enter and manipulate data on data sheets ... a requirement other users and I myself had -- is a problem without solution in Excel due to the de-facto non-existence of truly absolute references for real-world applications.

fair summary?

[romperstomper]

many thanks for your explanation.

You're welcome.

If you cut and paste, the destination cell also takes on the properties of the source cell, no?

No - the source cell is effectively moved to the destination which is destroyed.

OK, I get the second part (second sentence). But I DON'T have any formulas pointing to the source cell. I only have formulas pointing to the destination cell. And it shouldn't matter how the content gets there, i.e. whether it is
- pasted there after copying
- pasted there after cutting
- or typed in there manually

I don't think you did get what I was saying (or I am misunderstanding you). My point was to reiterate that the cell you cut gets moved, lock stock and barrel, to the new destination and replaces the one that was there. Hence any formula pointing at the old source location now points at the new location, not the original location (as would be the case if you copied).

In my case I have a file A.xls with 3 sheets. Sheet 1 (still in file A !) points to a cell in sheet 2 (still in file A !).
Now I open a file B.tab (tab separated values) and cut or copy a cell and paste it into sheet 2 of file A. The cell in sheet 2 now has a different value. But why would sheet 1 have a #REF! error now?
I can see, why there would be a #REF! error if I had anything pointing to a source cell discarded due to a cut+paste action. But I don't have any such reference to a disappearing source cell.

See above - it is not the source cell that is discarded (that to me would make no sense) it is the destination cell that is discarded.

In essence there is a decision to be made and MS chose one option, which you happen to disagree with:
Let's say you have four cells A1, B1, C1 and D1
A1: =C1
B1: =D1

now you cut and paste C1 to D1. What should happen? Presumably you would say that B1 should still point at D1 and A1 at C1? Or should B1 and A1 both point at C1? Or B1 have an error and A1 point at C1?
I personally concur with MS that I would want B1 to adjust to point at C1 since my intended source cell has been moved and equally I want some notification in A1 that my original setup has been messed with.

Having said that, I do like the idea of truly fixed references and will pass it on to the Excel team as a suggestion. Probably far too late for the next version, but they might contemplate it for a future release.

[romperstomper]

BTW, I don't really get your comparison of people to toilets. (well, I do, but not in this context. )