MAX(COLLECT FROM MULTIPLE SHEETS

Sam Lugiano

Because I maxed-out on multiple sheet references, I had to break up one large sheet into 3 smaller ones in order to collect the data I need. I am trying to get the last patient visit from 3 different sheets. The sheets are broken up by the year of the initial patient visit.

My formula works for some patients but not for others. I am getting an INVALID DATA TYPE error. I cannot find out why the formula works for some but not for others. There doesn't seem to be a pattern. My formula is below:

=MAX(COLLECT({Lebanon D/C & CL - PVTL: 2018 - 20 DOV}, {Lebanon D/C & CL - PVTL: 2018 - 20 BC}, BARCODE@row), MAX(COLLECT({Lebanon | PVTL D/C & CL 22 DOV}, {Lebanon | PVTL D/C & CL 22 BC}, BARCODE@row), MAX(COLLECT({Lebanon D/C & CL - PVTL: 2021 DOV}, {Lebanon D/C & CL - PVTL: 2021 BC}, BARCODE@row))))

I have also tried using IFERROR and that results in getting some LAST VISITS but not others. I have attached a section one of the data source sheets. I am trying to get the data from 3 different sheets that are all exactly alike.

Thanks in advance.

Paul Newcome

Try this syntax instead...

=MAX(MAX(COLLECT(.....)), MAX(COLLECT(.....)), MAX(COLLECT(.....)))

Sam Lugiano

Thank you.

I tried the below formula and I'm getting an #UNPARSEABLE. I added a third MAX( because I'm referencing 3 sheets. I'm thinking it's close.

=MAX(MAX(MAX(COLLECT({Lebanon D/C & CL - PVTL: 2018 - 20 DOV}, {Lebanon D/C & CL - PVTL: 2018 - 20 BC}, [BARCODE]@row))), MAX(COLLECT({Lebanon | PVTL D/C & CL 22 DOV}, {Lebanon | PVTL D/C & CL 22 BC}, [BARCODE]@row))), MAX(COLLECT({Lebanon D/C & CL - PVTL: 2021 DOV}, {Lebanon D/C & CL - PVTL: 2021 BC}, [BARCODE]@row))))

Paul Newcome

You have too many MAX functions and too many parenthesis. Try the syntax from my previous post and see if that works. Basically you want three separate MAX/COLLECT sets, separate them with commas, and wrap them in another MAX function.

Paul McGuinness

Hello @Sam Lugiano

As Paul advised above looks like parenthesis is the issue, its hard to confirm without having live sheets but based on the detail above the formula should look like this:

=MAX(MAX(COLLECT({Lebanon D/C & CL - PVTL: 2018 - 20 DOV}, {Lebanon D/C & CL - PVTL: 2018 - 20 BC}, [BARCODE]@row)),MAX(COLLECT({Lebanon | PVTL D/C & CL 22 DOV}, {Lebanon | PVTL D/C & CL 22 BC}, [BARCODE]@row)),MAX(COLLECT({Lebanon D/C & CL - PVTL: 2021 DOV}, {Lebanon D/C & CL - PVTL: 2021 BC}, [BARCODE]@row)))

Based on the sheet names etc. provided in your example.

Initially you were short a parenthesis and then you had 1 parenthesis too many in the middle of the formula so it was closing it off.

Does the above work?

Thanks

Sam Lugiano

Unfortunately not. I retyped the formula a couple times and still getting the #INVALID DATA TYPE error.

=MAX(MAX(COLLECT({Lebanon D/C & CL - PVTL: 2018 - 20 DOV}, {Lebanon D/C & CL - PVTL: 2018 - 20 BC}, [BARCODE]@row)), MAX(COLLECT({Lebanon D/C & CL - PVTL: 2021 DOV}, {Lebanon D/C & CL - PVTL: 2021 BC}, [BARCODE]@row)), MAX(COLLECT({Lebanon | PVTL D/C & CL 22 DOV}, {Lebanon | PVTL D/C & CL 22 BC}, [BARCODE]@row)))

Paul Newcome

Exactly how are the "DOV" ranges populated?

Is that error present anywhere in any of the sheets (after removing all filters and whatnot)?

Try putting each of the MAX/COLLECTS in their own cell for now to see if maybe it is just one that is causing an issue. That will help with further trouble shooting.

Is it possible the barcode is not present on one of the sheets?

Sam Lugiano

The "DOV" column is populated by an automation. Once the patient is marked discharged, the automation moves them to a Discharge Sheet. Which is this sheet and the column property still remains as a Date.

No filters exist on any of the sheets

I used MAX(COLLECT for each of the three source sheets and it correctly provided the LAST VISIT.

And I checked all sheets, there are no missing barcodes.

I can't imagine why this isn't working.

Paul Newcome

So each of the individual MAX/COLLECT pieces work on their own? Now try combining them into variations of two.

=MAX(MAX(COLLECT({Sheet 1})), MAX(COLLECT({Sheet 2})))

=MAX(MAX(COLLECT({Sheet 1})), MAX(COLLECT({Sheet 3})))

so on and so forth.

Sam Lugiano

Yes, each of the individual MAX(COLLECT worked on their own but both of the below returned an #INVALID DATA TYPE error. Not sure if I wrote the formulas correctly below based on your direction.

=MAX(MAX(COLLECT({Lebanon D/C & CL - PVTL: 2018 - 20 DOV}, {Lebanon D/C & CL - PVTL: 2018 - 20 BC}, BARCODE@row)), MAX(COLLECT({Lebanon D/C & CL - PVTL: 2021 DOV}, {Lebanon D/C & CL - PVTL: 2021 BC}, BARCODE@row)))

=MAX(MAX(COLLECT({Lebanon D/C & CL - PVTL: 2018 - 20 DOV}, {Lebanon D/C & CL - PVTL: 2018 - 20 BC}, BARCODE@row)), MAX(COLLECT({Lebanon | PVTL D/C & CL 22 DOV}, {Lebanon | PVTL D/C & CL 22 BC}, BARCODE@row)))

I'm thinking I should chop the source sheets into smaller sheets. The 3 sheets that I am using have 8,000 rows each.

Paul Newcome

What happens if you do sheets 2 and 3 combined? Both of those are referencing "2018 - 20" and both are throwing the error. What happens if we just reference the other two?

Sam Lugiano

Tried it. Still #INVALID DATA TYPE.

We have 7 offices and in 5 of the smaller offices I can put these visits all on one sheet and use one MAX(COLLECT formula but in the 2 larger offices I need to split up by year because of the cross-sheet formula max message.

I tried manually entering the MAX(COLLECT formula based on the year the patient treated. That mostly works but in a lot of cases a patient's treatment will carry over into the next year. So it will give me the LAST VISIT for that year but it may not be the patient's final LAST VISIT.

Since these patients are inactive the data on these sheets will never change and are only used for past performance analysis.

The LAST VISIT is correct before the automation moves them to this sheet. Is there a way to automatically remove the formula but keep the date when moved?

Sam Lugiano

I realized that if I remove the formulas from the sheet the LAST VISIT date will also carry over when moved. I just have to figure out how to correct the ones that were moved from prior years.