Hello,

Dahnovyn

I had a formula that was working all through the trials but then right before the roll out it stopped working!

In one cell there's a column that Auto counts Responses no big deal there.

In a cell in a different column

=MAX([Row ID]:[Row ID]) // this counts the largest value so I can record the latest response

In another cell and I've tried it in different columns I have

=INDEX([Lbs -Target]:[Lbs -Target], [Last Row Number]2)

// this is to copy whatever data matches the latest submission into this cell on the same sheet

These cells are then linked to another spread sheet where data is manipulated and calculated.

They worked for a week but then stopped pulling the latest values.

Also

No error message in the cells either just completely blank but when I make a whole new sheet it works fine until some days pass. Is there an alternative way to pull the latest information?

Any assistance greatly appreciated

Paul Newcome

Most should. It looks like you are trying to pull the most recent submission's data. In that case, a cross sheet formula would look like this:

=INDEX({Column To Pull Over}, MATCH(MAX({Row ID Column}), {Row ID Column}, 0))

Matt Lynn-PCG

@Dahnovyn

This is interesting.. I use something similar all the time. I wonder if it has something to do with sorting, or deleted rows etc, which wouldn't work well with just an index().

I would change it to

=INDEX([Lbs -Target]:[Lbs -Target], match([Last Row Number]2,[Row ID]:[Row ID], 0)

This would work better if rows were deleted or sorted better. I'd have to look at it directly to help further. Either some screen shots or we could setup a short screen share.

Paul Newcome

Are you able to provide some screenshots for context?

Dahnovyn

Thanks for the quick replies

I'll give this a try and test it out over the next few days here's a screen shot I'm pretty if you have any advice on how to organize the formulas that be a great help too. This sheet is linked to a form and its difficult to separate them from the sheet as anything you type in a cell beomes a submission unfortunately.

Paul Newcome

Have you thought about moving the data from the formula rows onto another sheet?

Dahnovyn

I did but the formulas do not work across different sheets.

Paul Newcome

Most should. It looks like you are trying to pull the most recent submission's data. In that case, a cross sheet formula would look like this:

=INDEX({Column To Pull Over}, MATCH(MAX({Row ID Column}), {Row ID Column}, 0))

Dahnovyn

For this I'm putting the Column Name where it says {Column to Pull Over} and the actual names of the columns for the Row IDs as well. But I'm getting an Unparseable error

Paul Newcome

Are you able to provide a screenshot of the formula open in the sheet as if you are about to edit it?

Dahnovyn

This Worked! Thanks I wasn't using the reference option I thought I had to type in the ID Column name but utilizing the reference link button helped out. I appreciate your insight and assistance. 🙏