LinkBack URL
About LinkBacks
I have a general spreadsheet with the initials of the employee (s) responsible in columns F to J and I want to transfer the tasks to an individual spread sheet for each employee leaving no spaces and new tasks going to the bottom of the list.
I have a general spreadsheet with the initials of the employee (s) responsible in columns F to J and I want to transfer the tasks to an individual spread sheet for each employee leaving no spaces and new tasks going to the bottom of the list.
Hello,
So first, you cannot do what you want with formulas or functions, this require VBA and that you rename the file to ".xlsm".
Is this acceptable?
Assuming yes, with spreadsheets, do you mean separate workbooks, or a new worksheet within the same workbook?
Transfer or copy? The former needs VBA, but the latter CAN be done with formulae. Which is it? If the former, I'll move this to the VBA section. Please confirm.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Which ever it is, show the expected output: is it simply columns A to E ?
And are new ALL tasks to be entered in "Sheet1" (MASTER) and transferred to the individual sheets OR are new tasks [which can /are allocated to more then one person/department) entered into each individual sheet?
And how are new tasks identified (to avoid duplication in the individual sheets)?
Last edited by JohnTopley; 06-26-2021 at 05:57 AM.
Pl See file.
Helper column H is used in all sheets except Sheet1
In H3 then copied down
In A3 then copied across upto column E
=IF(H2="","",IFERROR(AGGREGATE(15,6,ROW(Sheet1!$A$3:$A$70)/((Sheet1!$A$3:$A$70<>"")*((Sheet1!$F$3:$F$70=$A$1)+(Sheet1!$G$3:$G$70=$A$1)+(Sheet1!$H$3:$H$70=$A$1)+(Sheet1!$I$3:$I$70=$A$1)+(Sheet1!$J$3:$J$70=$A$1))),ROWS($A$3:$A3)),""))
=IF($H3="","",INDEX(Sheet1!A:A,$H3))
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks