Forum Discussion
Linking cells to a worksheet that is re-sorted
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.