Forum Discussion
Linking cells to a worksheet that is re-sorted
I know how to link a cell in one worksheet (#2) to another cell in another worksheet (#1), but what if changes are made to the other worksheet (#1), such has a row is inserted or the rows are re-sorted? The link doesn't get updated. Is there any way to do this? Any advice would be appreciated.
7 Replies
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.)
- Thank you so much for this reply. I am a teacher and I was having the same problem with an excel sheet in which I keep the students' grades. 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. I was taking the names from the first sheet and also taking information from different sheets to calculate different averages. I realized there was a problem when I had to reorder by name instead of by surname. The VLOOKUP function solved all the problems. Now I can reorder by any parameter and the data won't get all messed up. Thanks again.
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.
Thanks for your help.
The spreadsheet is pretty basic with basic formulas for adding/subtracting sick and vacation days.
I had to extract a certain group from the original worksheet (#1) to create a new worksheet (#2) as the rules are changing for that group. The data is not finalized yet in worksheet #1 (not all vacation/sick days are entered). And it could be some time before it's all updated. In the meantime, I need to set up the new group (worksheet #2) so that it's ready to go when the data is all finalized.
The target cell in worksheet #1 contains a simple formula "cell1 - cell2". Worksheet #1 is occasionally sorted by union group or years of service. I was using =sheet1!HG4. I tried using other variations like =sheet1!$HG$4 and =sheet1!HG$4, but that doesn't work either.
I've never used the lookup function before, but I'm sure I could figure it out.
I'm assuming from the name you've picked that you're in Human Resources, as I was during my career... I for a time had the overall responsibility for the HR database in the company, and did a lot of queries and Excel analyses of the (mainframe) data ...
Anyway, I'm wondering if it would be possible for you to upload here a sample (so long as it doesn't include actual names or other identifiable data), so I and others could take a look. In general, what we should be looking to do is have a central database that is stable in its design. It may take a while to get there...but if anything, new data should be added to the bottom of the existing table, rather than inserting rows and columns at various points in the middle. But to make relevant comments, it would help to see what you're actually working with.
Another thought--if you want to keep working on your own (an admirable trait; it's the best way to learn this stuff)--is to check out the FILTER function. Rather than actually moving a subgroup from Sheet1, this could be a way to keep the main database stable in Sheet1, for the entire population, but extract--for reporting or other purposes--ANY subgroup, one at a time. VLOOKUP is powerful for getting data on a single individual (or whatever single rows represent)...FILTER will get an entire population as defined by selection criteria.
