How do I bring over select fields from Sheet A to Sheet B if Sheet A's submitted date is today.
![Jeffrey_PMO](https://us.v-cdn.net/6031209/uploads/userpics/XT8S16OI62C0/nWKBCT94431PG.jpg)
Hey Smartsheet,
I have two Sheets and am trying to bring over Staffing Level, Collections Scheduled and Collections Absent from Sheet A
to Sheet B
if the submission was today for matching Departments. (Incases of multiple entries I want to bring over only the latest submission)
I've tried Max/Collect with Today formulas but cannot get the syntax right.
Thank you!
Best Answer
-
I would suggest setting the form to populate new entries at the top of the sheet. From there you would be able to use a standard INDEX/MATCH to pull in the most recent entry.
Answers
-
Is there a reason you are pulling this into another sheet instead of using a row report?
-
Hey Paul, good question. I have 100 different role types assigned to 32 departments to summarize each day.
Sheet A
is populated every morning via a Form by the 32 Departments and has over 100 columns for the role types. To make it easier to digest I builtSheet B
to show the Department and have nested that Departments specific employee roles underneath. -
Ah. Ok. So what happens when (as in your screenshot) you have the same department listed twice for the same day with two different staffing level colors?
-
Ideally the formula would select the latest entry for today.
For the example in the screenshot the formula would see that Today is 5/2 and would pull in 7:53am entry for Alliance Lab in
Sheet A
and populate the Staffing Level, Collections Scheduled and Collections Absent cells for Alliance Lab inSheet B
. -
I would suggest setting the form to populate new entries at the top of the sheet. From there you would be able to use a standard INDEX/MATCH to pull in the most recent entry.
-
Thank you Paul, I will follow that advice. I made the following formula below in
Sheet B
but I'm struggling where to enter the formula to ONLY pull fromSheet A
if that Submitted date is Today.=INDEX({DOSH|Collections Scheduled}, MATCH(Parent@row, {DOSH|Department}, -1))
I also tried an INDEX/COLLECT but receive an #UNPARASABLE.
=INDEX(COLLECT({DOSH|Collections Scheduled}, {DOSH|Department}, Parent@row, {DOSH|Submitted} =TODAY()), -1))
-
Got it to work by changing the = to , and the -1 to 1
=INDEX(COLLECT({DOSH|Collections Scheduled}, {DOSH|Department}, Parent@row, {DOSH|Submitted}, TODAY()), 1)
-
Glad you got it working. You also had an extra closing parenthesis on the end.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!