Returning last value in a column when rows are added

MelissaSan

Hi,

I am looking to return the last value in a column (could be blanks in between or if easier we could add "0") and the rows are added on a regular basis.

In this example, I would want to return the values in the red boxes and I'm either going to link them to another sheet or add additional columns that just store the most recent entry at the top of my sheet.

I've tried INDEX but i'm doing something wrong b/c I think it selects the entire column even when data is not in it.

To provide more background on this requirement:

For Actual # of Sites - today it would return 11 but if someone goes in and adds a value to Nov and Dec row of say 22 and 45, I would want the 45 to return.

Any help is appreciated!

Nick Korna

Use something like this:

=INDEX([Actual # of sites]:[Actual # of sites], COUNTIF([Actual # of sites]:[Actual # of sites], <>""))

This matches the row with the highest filled value but it only works assuming you have no blanks between them (e.g. if someone filled in Dec 2023 but not Nov 2023) it would not find the last value.

MelissaSan

Thanks, it works but only once.

It returned 11 but then I added values for Nov and December and it did not update.

MelissaSan

Hi,

I actually got it to work by using this:  

=INDEX([Actual # of Sites]:[Actual # of Sites], COUNTIF([Actual # of Sites]:[Actual # of Sites], <>"")+1)

It works, but is this the best solution?

Thanks!

Nick Korna

The +1 implies you may have at least one blank row somewhere - the previous 11 would be returned because some of the previous rows have this value.

You can see this in these screenshots:

Here there are 5 values, so the 5th row is 5.

Here there are 6 none blank rows, but because the 6th row is blank, no visible value is returned.

Here there are 7 rows with numbers, but the 7th one is 6 (value wise). More blank rows would produce a bigger offset.

MelissaSan

Thanks, that makes sense.

Would you recommend me making sure there are no blank values or is there a better way to write the formula to account for x blank values?

Nick Korna

If your data missing is a only a problem in the past (presumably this would only apply to actual), add a date column with end of month/start relative to the Month column.

You can then use:

=INDEX([Actual # of sites]:[Actual # of sites], (COUNTIF([Actual # of sites]:[Actual # of sites], <>"") + COUNTIFS([Month helper]:[Month helper], <TODAY(), [Actual # of sites]:[Actual # of sites], "")))

Equally you can set a cell/sheet summary to alert you to how many of these there are using the last bit of that formula:

=COUNTIFS([Month helper]:[Month helper], <TODAY(), [Actual # of sites]:[Actual # of sites], "")

If you're really going for it, you can add some filters and/or conditional formatting to highlight these even further.

The formulas from the previous posts should be fine for the planned column types - presumably there you're only forecasting a certain amount of time in the future and can backfill any blanks which likely shouldn't really be there.

MelissaSan

Hi,

Thanks for the additional information and options! I'll consider implementing - some columns that have blanks are actually correct because a clinical trial has not started yet.

In hindsight, this sheet really should have been set up differently but I'm helping someone with a sheet that has already been established.

Thanks for all your help!

Nick Korna

In which case your options would be to either to narrow the range, rather than it being the whole column. For example, just be rows 1-10, if a given trial was only running for the first 10 months or fill in old blanks as 0.