Cells referencing other cells in another worksheet.

[CrabbyHorse]

Hi. I need some help with cells referencing other cells in another worksheet. 

The initial setup works fine. For example the first worksheet has a column containing the alphabet in order. I reference those cells in another worksheet so I get another identical alphabet column. I go back to the first worksheet and mess about with the order of the letters. E.g. add a blank row somewhere; move a row further down the sheet somewhere etc. 

When I go to the second worksheet again, all the dynamic referencing is maintained so my alphabet is still in the correct order. So far so good.

But when I now sort the first worksheet back into alphabetical order (using the data sort utility) the dynamic referencing in the second worksheet is lost and the alphabet is now all out of sequence (as though I had used fixed referencing with $ signs). How do I preserve the dynamic referencing without having to perform the sort manually? Any ideas?

[#2]

Sorry; by dynamic and fixed referencing I meant 'relative' and 'absolute'.

[#3]

You have to extract the alphabets in Sheet1 to Sheet 2 and sort them in ascending order. For example, your column labels in both Sheets are in Row 1 and your data begins in Row 2, the formula in Sheet2!A2, copied down rows, is:
=IFERROR(LOOKUP(2,1/(COUNTIF(Alphabets,">="&Alphabets)=MAX(INDEX(
COUNTIF(Alphabets,">="&Alphabets)*(COUNTIF(A$1:A1,Alphabets)=0),0))),
Alphabets),"")
In the foregoing formula, Alphabets is a range name defined as:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

[#4]

Hi @Twifoo 

Wow it works! Thank you!

But it's all magic to me. I was looking for something much simpler since I would struggle to fix it if I need to modify it for any reason.

To be honest, I usually use "Numbers" on my Mac but I had the same problem there.  And the Apple

Help desk couldn't help me but thought the Numbers software needed to be fixed (they could be scratching their heads right now!)

Hence I am on a free trial of MS Office Excel system.  

What I am doing is using spreadsheets as a simple relational database, each sheet representing a normalised table. And I was getting on fine until I ran into this problem. I don't think I can transplant your solution to Numbers because it doesn't seem to support Range Names for a start.

My other option is to use MS Access or SQLite on the Mac but I think the learning curve might be too steep at my age!

So for now, I am just heading up each relevant sheet with 'Do Not Sort!'. For example it would have been nice to sort a 'person' table by either surname or first name, but I would always have to 'Undo Sort" to maintain data integrity. 

Let me know if you think of any simpler ways of achieving this objective.

Thanks again.

John

[#5]

I find it easy to manipulate formulas in MS Excel. I advise you to you use it. If you encounter issues, you can always post it here. I’m sure such issues will be resolved soon, not necessarily by me.