Sorry guys but I don't even know how to search forums for this specific request.
Here goes....
Purpose: Create an organic sheet to track who is assigned what equipment spread across multiple cells. This needs to function regardless of which filtered column is re-sorted at any given time. Meaning, the formulas all stay with their respective cells even if those cells move temporarily when column whatever is sorted alphabetically and then maybe later I sort different column whatever numerically. I want to be able to update the roster sheet as soldiers come and go, and their name is then copied to the respective cells on the other sheets. Then at the same time, if a soldier leaves, I delete his name from the cell on the Roster worksheet and it is then removed from the other sheets, AND on the Roster sheet, if columns b-e have a blank name cell (column A), their values also become blank because nobody is assigned a weapon in that corresponding cell on the other sheets.
Here is how the workbook is setup:
5 Worksheets: Roster, M16A2, M9, M2, M240B
On Roster sheet: Columns A-E are: Name, M16A2 ASSIGNMENT, M9 ASSIGNMENT, M2 ASSIGNMENT, M240B ASSIGNMENT
EXAMPLE:
From Roster worksheet:
John Doe is in cell A14 and Jane Smith is in A 32. (Note the max rows will be 32 on all sheets)
John Doe's columns read A06 (he is assigned the weapon on the M16A2 sheet labeled A06), P-04 (he is assigned pistol 04 on the M9 sheet), blank, blank (he is not assigned an M240B or M2)
I deleted Jane Smith since she left the unit and now her weapon assignment columns have all blanked themselves
Now, those alpha-numeric assignments are populated based on excel searching for the value of their name from Roster sheet column A, across all the other sheets. Column A on the other sheets will always correspond to that weapon name (A06, P-04, etc)
I hope this all makes sense. I can upload screenshots if necessary, but I will have to remove serial numbers and personal info first.
Thanks for all the help guys!
Here goes....
Purpose: Create an organic sheet to track who is assigned what equipment spread across multiple cells. This needs to function regardless of which filtered column is re-sorted at any given time. Meaning, the formulas all stay with their respective cells even if those cells move temporarily when column whatever is sorted alphabetically and then maybe later I sort different column whatever numerically. I want to be able to update the roster sheet as soldiers come and go, and their name is then copied to the respective cells on the other sheets. Then at the same time, if a soldier leaves, I delete his name from the cell on the Roster worksheet and it is then removed from the other sheets, AND on the Roster sheet, if columns b-e have a blank name cell (column A), their values also become blank because nobody is assigned a weapon in that corresponding cell on the other sheets.
Here is how the workbook is setup:
5 Worksheets: Roster, M16A2, M9, M2, M240B
On Roster sheet: Columns A-E are: Name, M16A2 ASSIGNMENT, M9 ASSIGNMENT, M2 ASSIGNMENT, M240B ASSIGNMENT
EXAMPLE:
From Roster worksheet:
John Doe is in cell A14 and Jane Smith is in A 32. (Note the max rows will be 32 on all sheets)
John Doe's columns read A06 (he is assigned the weapon on the M16A2 sheet labeled A06), P-04 (he is assigned pistol 04 on the M9 sheet), blank, blank (he is not assigned an M240B or M2)
I deleted Jane Smith since she left the unit and now her weapon assignment columns have all blanked themselves
Now, those alpha-numeric assignments are populated based on excel searching for the value of their name from Roster sheet column A, across all the other sheets. Column A on the other sheets will always correspond to that weapon name (A06, P-04, etc)
I hope this all makes sense. I can upload screenshots if necessary, but I will have to remove serial numbers and personal info first.
Thanks for all the help guys!
