Copy cell from other worksheet cell based on values in that worksheet's column


New Member
Mar 18, 2016
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


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!

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...