I have a Google Sheet of 6 tabs. The intent is to create a role cost calculator and compare predicted to actual use. One tab contains my master user list. It is the software used tab, just fyi. Another tab is a slave tab which is the hardware used. That slave tab gets the user list from the software tab with the formula ={SoftwareByUser!A4:D160} For each user, their hardware columns, E and beyond, are manually populated with integers that get multiplied by column costs to get a per user cost. When an employee leaves our company I'd like to delete their row on the software tab. Imagine going from 51 total employees to 50 by removing row 26. Row 27-51 correctly become 26-50 on the software tab. The trouble is the corresponding hardware row is not in removed, still having 51 rows in columns E and beyond, even though the number of rows in columns A-D have been reduced by the formula to the correct number of rows, 50. Hardware rows 26-51 no longer match their user. I'm asking for a solution to do what I describe please. Is my thinking faulty and a different approach necessary?
1 Answers
What you should have to do is define an ID for each user (can be at least a first name / last name / date of birth concatenation). Then on the slave tabs manually type each one (do not link the two tabs for ID), then you can recall the master to slave tab using VLOOKUP with multiple column numbers at once. If one line is erased in master, or if the master is sorted, this will not affect the slave tab unless giving you an alert that the line has been erased.
Related