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
Ankur Teotia

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...