Cross-Sheet formulas with Max Date

MelodyV

I'm a new user and trying to use a web content form widget to filter a report on a dashboard. I've got the form built and the form widget working on the dashboard. The form selections go to this Sheet A, where I have it set up that the box will be checked for the most recent selection.

The report that I want to be filtered on the dashboard pulls from Sheet B below (only relevant columns shown):

On Sheet B, I want the boxes to check if they match the most recent selection from Sheet A. (That way I can filter Sheet B and Report B based on the checkbox, then add to dashboard, thereby making my dashboard interactive when the user selects an option from the Sheet A form that is published on the dashboard... I think...) I have the checkboxes in Sheet B working, but it doesn't update whenever a new selection is made. It just keeps the reference to the original cell, not the most recent one. Here is the formula I am using:

=IF(Area@row = {Selected Strategic Priority Range 1}, 1, 0)

Where {Selected Strategic Priority Range 1} refers to the Strategic Priority Column on Sheet A.

So here, even though Sheet A has been updated to show that Employment & Work Supports is the most recent result from 11:53pm, the formula on Sheet B is pulling the Quality Housing result from 11:27pm, which I used to set up the formula cross-sheet reference, instead of the most recent. Can someone point me in the right direction with my formula for the checkboxes on Sheet B? Please keep in mind I am a beginner! I feel like I am so close, and have been reading forum posts for hours, and I cannot get this figured out. Thank you for any and all help.

KPH

Hi again

This could be what you need. I hope it is!

1)

You have sheet A with Created Date and Strategic Priority like the screen shot below. Each Strategic Priority appears multiple times. You also have formula to check a box against the latest date.

=IF([Created Date]@row = MAX([Created Date]:[Created Date]), 1)

I do not use this in the work that follows so you could delete that if you like. It was a great help in understanding the requirement and good to show the right values are selected, but not needed, if you don't want to keep it.

2)

You have another sheet B with a column called Area that contains the same vales as Strategic Priority and also duplicates these. You want to check a box next to all instances of a value that was created last on sheet A.

Like this:

3)

If I have understood that correctly, you can do this by using this formula to check the checkbox on sheet B.

=IF(Area@row = INDEX({Selected Strategic Priority Range 1}, MATCH(MAX({Melody Sheet A Created Date}), {Melody Sheet A Created Date})), 1)

Where {Selected Strategic Priority Range 1} is the Strategic Priority column (which I believe you have already cross referenced) and {Melody Sheet A Created Date} is the created date column.

This formula says that IF the value in the Area column in this row is the same as the value in the Strategic Priority column in sheet A where the Created Date matches the maximum of all the Created Dates, then put a 1 (i.e. tick the box).

Here it is in color

MelodyV

I am also experimenting with using MAX and Collect. I have tried it with the second range referring to the Checkbox on Sheet A:

=IF(Area@row = (MAX(COLLECT({Selected Strategic Priority Range A}, {Selected Strategic Priority Range Checkbox}, 1))), 1, 0)

And I have tried it with the second range referring to the latest Created Date on Sheet A:

=IF(Area@row = (MAX(COLLECT({Selected Strategic Priority Range A}, {Selected Strategic Priority Range Created Date}, 1))), 1, 0)

Both are returning results, but the results are wrong.

KPH

Hi @MelodyV

I think I understand what you are trying to do and I feel that a combination of INDEX and MATCH could achieve the result you want, where IF cannot. Have a look at those functions and if they don't solve the problem, can you provide a few more details:

How are you checking the box on sheet A? (It sounds like you have an IF MAX COLLECT formula - can you share it)

I can see that Area and Strategic Priority are common across both sheets. Do both sheets have multiple instances of the same row?

MelodyV

Hi @KPH,

I am basically needing a formula that will pull the most recent response for "Strategic Priority" column in Sheet A, compare it to the "Area" column in Sheet B, and if they match, check the box in Sheet B. Right now I am using

=IF(Area@row = {Selected Strategic Priority Range 1}, 1, 0) but it's only pulling from the original cell I used to make the formula. It is not pulling the latest created as new rows are added to the top of Sheet A. I also tried MAX and COLLECT a few different ways, but I am very new so could be doing it wrong.

To answer your specific questions, in Sheet A, the formula to check the box is: =IF([Created Date]@row = MAX([Created Date]:[Created Date]), 1, 0)

Both sheets have multiple rows of Employment & Work Supports, Quality Housing, Agency of Excellence, etc. These are under "Strategic Priority" in Sheet A and "Area" in Sheet B - the ones I'm trying to get the box to check if they match.

Thank you!

KPH

Hi

How do you determine if the "Strategic Priority" column in Sheet A matches "Area" column in Sheet B? Both have multiple instances of the same thing. Is there another column, or do you want all of the instances on sheet B to be ticked?

You have the correct formula to identify and tick the most recent row on sheet A:

=IF([Created Date]@row = MAX([Created Date]:[Created Date]), 1)

but I am not sure I understand what you want to do with this. I can write up an answer for something that could help,

Edited to add: I just saw your other post, so I think this answer below could be it...

KPH

Hi again

This could be what you need. I hope it is!

1)

You have sheet A with Created Date and Strategic Priority like the screen shot below. Each Strategic Priority appears multiple times. You also have formula to check a box against the latest date.

=IF([Created Date]@row = MAX([Created Date]:[Created Date]), 1)

I do not use this in the work that follows so you could delete that if you like. It was a great help in understanding the requirement and good to show the right values are selected, but not needed, if you don't want to keep it.

2)

You have another sheet B with a column called Area that contains the same vales as Strategic Priority and also duplicates these. You want to check a box next to all instances of a value that was created last on sheet A.

Like this:

3)

If I have understood that correctly, you can do this by using this formula to check the checkbox on sheet B.

=IF(Area@row = INDEX({Selected Strategic Priority Range 1}, MATCH(MAX({Melody Sheet A Created Date}), {Melody Sheet A Created Date})), 1)

Where {Selected Strategic Priority Range 1} is the Strategic Priority column (which I believe you have already cross referenced) and {Melody Sheet A Created Date} is the created date column.

This formula says that IF the value in the Area column in this row is the same as the value in the Strategic Priority column in sheet A where the Created Date matches the maximum of all the Created Dates, then put a 1 (i.e. tick the box).

Here it is in color

KPH

If I add another row to sheet A, like this

Sheet B does this

I hope this is what you want!

MelodyV

Oh, KPH, you are a lifesaver!!! It worked!! I was just about to scratch the whole idea and make a bunch of separate Sheets/Reports and just add them as linked. Thank you for this solution! My dashboard is now interactive!!

KPH

💃(Happy dance)

I'm so glad we go there before you gave up.