I need a formula that will automatically move to the latest row with data.

Nikki G

I believe I have to use "Max Helper" but do not know the formula so it will automatically move down to the next row when the latest data is entered. The data in the latest row will automatically update in summary rows. Currently, I have to manually select which rows data I want in the summary selecting the "Max Helper".

Samuel Mueller

@Nikki G sorry I missed a comma, these should work

Row 16:

=iferror(index(collect([Major Upcoming Activities]18:[Major Upcoming Activities]50,[date update]18:[date update]50, max([date update]18:[date update]50)),1),"No Data")

row 15:

=iferror(index(collect([status update]18:[status update]50,[date update]18:[date update]50, max([date update]18:[date update]50)),1),"No Data")

Samuel Mueller

So every week, a new row gets added, and you want to take that most recent row text, and put it in the summary for major upcoming activities?

try something like below. I can't see the row numbers on your screenshot so adjust 15 and 50 accordingly. They need to be the rows in the weekly status update section.

=index(collect([major upcoming activities]15:[major upcoming activities]50,[date update]15:[date update]50, max([date update]15:[date update]50),1)

Nikki G

Yes, that's correct. The most recent row text need to be auto-populated in the Major Upcoming Activities column, with the status update column's data in row 15 and Major Upcoming Activities column's data in row 16.

Where do I enter that formula?

Samuel Mueller

@Nikki G try putting this formula in the "major upcoming activities column in row 16. Let me know what happens. You may want to copy and save the formula that's already there in case you want to go back

=iferror(index(collect([major upcoming activities]18:[major upcoming activities]50,[date update]18:[date update]50, max([date update]18:[date update]50),1),"No Data")

Nikki G

Error above on right side.

We need row 15 and 16 under "major upcoming activities" column updated with each new entry/row. I feel like it's something to do with the "max helper" column checks.

Samuel Mueller

@Nikki G you need to add that formula directly to the cell, not in the ai formula generator. For the status field row 15 it would be

=iferror(index(collect([status update]18:[status update]50,[date update]18:[date update]50, max([date update]18:[date update]50),1),"No Data")

Samuel Mueller

Alternatively you can leave it how it is, and just create an automation to check the max helper box when status helper or major upcoming activies change

Nikki G

here is the formula that is currently in the cell:

=iferror(index(collect([status update]18:[status update]50,[date update]18:[date update]50, max([date update]18:[date update]50),1),"No Data")

Below is the new formula and result:

Nikki G

Oooo great idea. I may have to do the automation

Samuel Mueller

@Nikki G sorry I missed a comma, these should work

Row 16:

=iferror(index(collect([Major Upcoming Activities]18:[Major Upcoming Activities]50,[date update]18:[date update]50, max([date update]18:[date update]50)),1),"No Data")

row 15:

=iferror(index(collect([status update]18:[status update]50,[date update]18:[date update]50, max([date update]18:[date update]50)),1),"No Data")

Nikki G

YEEES!!! Thank you SOOOO much!!

Samuel Mueller

@Nikki G just keep in mind you may have to update the "50" part of these formulas if you are going to have a lot of weeks entered. Once row 51 is entered the formula won't see it. I'm not sure how long your project is, but as is you will get about 32 weeks if one row added per week before you have to update the formula.

Nikki G

If we go past 50 rows, what do I enter? 100?

Samuel Mueller

The row has to be on the sheet, when you get to 50 I think it's 60. You can also manually add like 100 rows, and then delete the content but not the row, and you could use whatever the max row is in the formula.

Nikki G

Thank you!!