Forum Discussion

GiulianoZ's avatar
GiulianoZ
Copper Contributor
Jun 03, 2025

Shifting Row information to a new worksheet

I have a workbook where I am trying to create taskings for individual staff.

I am wanting to setup the following, but I am limited on not being able to use macros, as Excel is controlled by HO

 

What I want to achieve is the following:

Select an item for a drop down list (already created with staff names), and then once a name is selected, the whole row duplicates to a different sheet (which is the staff member's name) and fills the next available free row

Sheet 1: The drop down list contains people's names and is in Column C

Sheet 2-15: The sheets available are the corresponding names as in the dropdown list.

For example:
Drop Down List name = Tom Brady
Sheet Name = Tom Brady

So if Tom Brady is selected in Sheet 1, Column C, then the row that name is in, is duplicated to the sheet that has been renamed to Tom Brady, and fills the next available row after row 8

 

Any help on this is greatly appreciated.

developer
excel
Formulas and Functions
Macros and VBA

2 Replies

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jun 04, 2025

    If Sheet 1 has a header row, copy it to each of the staff name sheets.

    In the following, I'll assume that the data is in columns A to Z.

    Let's say Sheet 2 is named Tom Brady.

    In A2 on Sheet 2:

    =IF(COUNTIF('Sheet 1'!C:C, "Tom Brady")=0, "", LET(f, FILTER('Sheet 1'!A:Z, 'Sheet 1'!C:C="Tom Brady"), IF(f="", "", f)))

    Replace Sheet 1 with the real name of that sheet.

    Similar for the other sheets.

    • GiulianoZ's avatar
      GiulianoZ
      Copper Contributor
      Jun 04, 2025

      Right. It turns out that I have found an issue. I actually only need specific columns to move across, and that one of the columns has conditional formatting, which I also need to move across. 

      I have created headings and headers that use the first 9 Rows on the 'Working Document' worksheet and 8 rows on the subsequent 'Staff Name' worksheets.

      The data that needs to move across starts at A10 on the first worksheet 'Working Document', and then the subsequent sheets will be staff names such as 'Tom Brady'.

      The data to be duplicated is coming from Column A on 'Working Document' to Column A on 'Tom Brady'. The row on Tom Brady starts at Row 9 and the fills the next available row. Then the same for Column B (where the conditional formatting is), and then Column G.

       

      Basically, the gist of it is this. If a staff member has a task assigned to them on the 'Working Documents' worksheet, as soon as their name is selected in the dropdown box, it duplicates the required data (Columns A, B, and G) to the worksheet that has their name on it. If a staff member has no tasks assigned, then the formula error is hidden (Can't see a #CALC! or any other error).


      Hope this explains it... Although I think I have confused myself now. 🤦‍♂️
       

Resources