How do I bring over select fields from Sheet A to Sheet B if Sheet A's submitted date is today.

Jeffrey_PMO

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!

Paul Newcome

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.

Paul Newcome

Is there a reason you are pulling this into another sheet instead of using a row report?

Jeffrey_PMO

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.

Paul Newcome

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?

Jeffrey_PMO

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 in Sheet B.

Paul Newcome

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.

Jeffrey_PMO

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 from Sheet 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))

Jeffrey_PMO

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)

Paul Newcome

Glad you got it working. You also had an extra closing parenthesis on the end.