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.)
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.