Forum Discussion
Linking cells to a worksheet that is re-sorted
Others may have a different answer. I was not able to "shake the link" by inserting row or column--when I did those things the reference followed the original cell and stayed "reliable."
The link did break when I sorted the table in which the piece of data appeared. So I could see how sorting would affect such a reference.
I tried naming the range (the cell) to see if a name would stay with the original cell; it didn't.
That said, let me ask you a question (maybe a few):
- What's the nature of the data array in your Sheet #1? Is it a standard Excel Table?
- What's the nature of the reference you're making? I'm presuming it's something along the lines of =Sheet1!C13 or some variation on that.
- If the answer to my first question is that it IS a Table, then why not use a lookup function, referring to the first column to identify the row, and then an offset? That way the reference will remain dynamic and adjust.
What I'm getting at--and those may not be the most definitive questions--is an attempt to look at the bigger picture and see if we can re-design the workbook to preclude the conditions or circumstances that create your problem. So if you could describe the overall purpose of your workbook, why you're inserting rows or columns and resorting the data...there may be other solutions that would make the whole work more effectively.
(And perhaps you could upload a sample of the sheets in question, so long as there's no confidential or private information on your sample.)
I use the same workbook for the whole duration of the programme (2 or 3 years) and I divide it into sheets: one sheet per semester.
Having multiple sheets that are essentially identical except for (in this case) the time period covered--that's a method that makes it clearer to the human mind and eye, but in many ways gets in the way of some of Excel's marvelous abilities to work with single, well-designed databases. I would encourage you to think of redesigning your workbook so that all semesters are recorded on a single sheet--just add a column that records some sort of specific semester designation (1S2023, 2S2023,1S2022....; whatever works).
I'd be happy to explain further if this makes a bit of sense.
- mathetes
What you say makes a lot of sense. However, putting everything into the same tab could be overwhelming for the person using it. I will explore it, and share some screenshots (or the whole file even) with you, and see if we find some better way.What you say makes a lot of sense. However, putting everything into the same tab could be overwhelming for the person using it.
Again, if this wasn't clear from my first post, that "everything in the same tab" only applies to what we might call the "raw data" or Input section of the workbook. It does not apply to the Output, the summary reports, the analytical view of trends etc.
The point of a single consolidated database is that it makes that aspect--the Output--easier for both Excel and the user. Keeping things separate at the "raw data" level actually make it harder to summarize, to see big picture conclusions, etc. Yes, you may need to learn to use some of the features of Excel that enable these summaries from a single database, but that should be something that a teacher aspires to, don't you think?