Forum Discussion
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 ...
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.
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. 🤦♂️
