Return a value based on the MAX date in a Child row

SherriL

Hi,

I've looked at many INDEX/COLLECT postings on here, and I just can't seem to find what I need.

I want to pull the value from a particular column based on whether that value is in a row that is the MAX DATE for that group of child records.

The screenshot below shows a sample "Patient". We create a parent row, then child rows for each of their subsequent visits. Notice in the "Sessions Remaining" column, I calculate how many sessions they have remaining from what was authorized. (Note, the first/evaluation session doesn't count, so my numbers aren't off.)

I want to have a cell on the parent row indicate how many sessions they currently have left based on the last DOS (date of service). Therefore, the cell on Mickey's parent row would currently indicate 62.

Your help is much appreciated. I'm sure I'm just mixing up the order and/or references I'm using.

Paul Newcome

Try this:

=MIN(COLLECT(CHILDREN(), CHILDREN(DOS@row), @cell <> ""))

ericncarr

@SherriL

You can do an Index(collect()) to pull the number of Sessions remaining based on the primary column equaling the primary column @row, and then the DOS equaling the Max DOS from the DOS range.

=INDEX(COLLECT([Sessions Remaining]:[Sessions Remaining], [Primary Column]:[Primary Column], =[Primary Column]@row, DOS:DOS, MAX(DOS:DOS)), 1)

SherriL

Thanks @Paul Newcome - Your function operates, but it returns 0. I'm not sure how your formula knows to return the Sessions Remaining #.

Thanks @ericncarr - I get #unparceable from yours. I'm assuming the reference to the primary column is what's supposed to direct the function to find a child record? Unfortunately, some clients can have more than one parent record, so I'm not sure if that would impact it.

FYI - here is something I was trying, and I get a #BLOCKED error.

=INDEX(COLLECT([Sessions Remaining]:[Sessions Remaining], DOS:DOS, MAX(CHILDREN(DOS@row)), 1))

I don't know if this is helpful - I already find the last DOS entry and record it in a field on the parent record. So "Last Session Date' (which isn't shown in the screenshot) indicates 03/06/2023 for Mickey. I tried formulas trying to match, within the child records, to this Last Session Date, then give me the Sessions Remaining. Can't get that to work correctly either.

Paul Newcome

=MIN(COLLECT(CHILDREN(), CHILDREN(DOS@row), @cell <> ""))

This is basically saying to collect all of the child rows where the child rows of the DOS column are not blank and then pull the lowest number.

Where exactly are you putting the formula?

SherriL

@Paul Newcome, I'm putting this in a cell on the parent row. This is NOT in the Session Remaining column; it's in a column just for this calculation.

I just used your formula above and I still get 0 as a result. I tried it on a couple of parent records - all zero.

UPDATE:

I see that your formula works when I enter it in the Sessions Remaining column. Unfortunately, the way my sheet is setup I can't put it there because I have a formula in that column to create the countdown of sessions. Therefore, I need this to work from another column.

Paul Newcome

In that case we just need to reference the Sessions Remaining column inside of that first CHILDREN function.

=MIN(COLLECT(CHILDREN([Sessions Remaining]@row), CHILDREN(DOS@row), @cell <> ""))

SherriL

This worked!! You made my month!! Thanks so much.

Paul Newcome

Happy to help. 👍️