Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Pivot view of data into another sheet
I have organized my original sheet by Client name to show the multiple people (contact list) working on that job along with their start dates. I would like to create a second sheet to show organized by people all of jobs they are working on and when to see if they are overloaded during a timeframe. Sometimes the people could take on different roles (see "Steve" below) How can I pivot this information into a new sheet to capture all clients "Steve" is associated with regardless of where his name appears? And also include any new clients added to the original list or if a job is reassigned to "Steve" from someone else?
Original Sheet:
Desired new view for new sheet:
Really appreciate any help!
Best Answers
-
Oh. Sorry. I misunderstood what you were needing.
=IF(COUNT(CHILDREN(Job@row)) = 0, JOIN(COLLECT(Manager@row:Staff@row, Manager@row:Staff@row, @cell <> "")))
Edit to add: This can be applied as a column formula.
-
You would do a nested IF statement like so:
=IF(Supervisor@row <> "", "Supervisor", IF(Charging@row <> "", "Charging", ................))))
Then to get it to only apply to the child rows you would wrap the whole thing in another IF statement like so:
=IF(COUNT(CHILDREN(Job@row)) = 0, ......................................................................)
Answers
-
I would insert a helper column with this column formula in it:
=JOIN(COLLECT(Manager@row:Staff@row, Manager@row:Staff@row, @cell <> ""))
Then you can create a row report based on this sheet and group by the helper column.
-
Thank you! Now, how can I have that apply only to the CHILD rows?
I tried =JOIN(COLLECT(CHILDREN(Manager@row:Staff@row)), (CHILDREN(Manager@row:Staff@row)), @cell <> "") but I get "incorrect arguement"
-
I'm not sure I follow. I don't see any indentation in either of your screenshots, so I don't know exactly where you are wanting the formula and what exactly you are wanting to pull.
-
Sorry, I quickly created that example separate from my sheet so I wasn't using actual info. I have a parent row for Company A, Company X in a row above each of those groupings. So this repeat with the name scheduling out are the child rows beneath.
-
-
Since all names roll up to the parent row, I want to exclude that row in the formula
-
Ah. Ok. Try this in the parent rows:
=JOIN(COLLECT(CHILDREN(), CHILDREN(), @cell <> ""))
-
No, I want the parent row of my "Join" column to stay blank. Otherwise it returns a value of JeffAnneSteveLisa with the formula you gave directly above. I want to apply your first formula only to the child rows in the column and the cell of the parent row to stay blank.
My parent rows above already pull up the assignee to the row which is what I want, I just don't want them in the join column
This is what I get:
This is what I want with parent blank in join column - but all I did was delete the formula from the parent row. Is there a way to automate so if new Company's are added the formula will apply?
Sorry this is so complicated!
-
Oh. Sorry. I misunderstood what you were needing.
=IF(COUNT(CHILDREN(Job@row)) = 0, JOIN(COLLECT(Manager@row:Staff@row, Manager@row:Staff@row, @cell <> "")))
Edit to add: This can be applied as a column formula.
-
Perfect! Thank you so much!!
-
I'm trying to take this a step further and have another helper column identify the job title of the employee based on the column title their original name falls under. So if the Supervisor column is not blank, insert the word "Supervisor" into my helper column, etc. And have it only apply to the child rows like before. I tried this but not working:
=IF(COUNT(CHILDREN([SUPERVISOR]@row)) <>, "SUPERVISOR", IF(COUNT(CHILDREN(CHARGING@row)) <>, "CHARGING", IF(COUNT(CHILDREN(STAFF@row)) <>, "STAFF"
It returns as #unparseable. Are you able to help?
-
You would do a nested IF statement like so:
=IF(Supervisor@row <> "", "Supervisor", IF(Charging@row <> "", "Charging", ................))))
Then to get it to only apply to the child rows you would wrap the whole thing in another IF statement like so:
=IF(COUNT(CHILDREN(Job@row)) = 0, ......................................................................)
-
Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 207 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!