Make a bottom row automatically equal the nearest row?

I have a 53-row 16-column spreadsheet. As each week of the year goes by another row gets filled with data from 16 individuals.

I want the bottom row to always represent a person's current score (since we're in week 9 of the year, cell A53 currently has the formula =A9).

However, when week 10 comes and the tenth row gets filled around I will have to change the formula to =A10. I would like to find a way to automatically select the bottom filled row rather than having to change the formula every week.

I have seen the related question Automatically select last row in a set in Excel but that solution did not work for me.

-
Is the score incremental, so =MAX(A:A) could work? – Paul Feb 16 '13 at 9:09
is it always tied to the date? – mcalex Feb 16 '13 at 9:17
The score is not incremental. I am not exactly sure if I understand the 'tied to date' question correctly, but the dates themselves are not in the spreadsheet. They're just represented in the fact that row 1 = Week 1, row 2 = Week 2 and so on. – user1205197 Feb 16 '13 at 9:20
re: dates: does the sequence/game always start in the first week of the year? – mcalex Feb 16 '13 at 9:23
Yes, always starts in the first week of the year and runs for all 52 weeks. – user1205197 Feb 16 '13 at 9:37

Assuming data is numeric try LOOKUP, in A53 copied across:

`=LOOKUP(9.9E+307,A1:A52)`

-

You don't need any extra add-ons installed - just type this in `A53`:

`=INDIRECT("A"&COUNTA(A\$1:A\$52))`

For your case it will return the last non-empty value in range `A1:A52`. You may correct the range as you like, but make sure you keep row references absolute to avoid errors in case formula is moved down. However, you may autofill it to all 16 columns - only column will cycle.

-

If you install the Analysis toolpack, you have access to the WeekNum(serial, return) function.

In the case that the program always starts in week one of the year, you could set up an Indirect statement that uses WeekNum() to select where to get info from. Something like:

=Indirect(Concatenate("A", WeekNum(Today()))

in the cell where you want the latest data. If the program starts at another time, you could work out which week of the year it started, and subtract this from the WeekNum() part of the formula. Eg if you start in week 10 (so week 11 results are in A3 assuming a header in row 1):

=Indirect(Concatenate("A", WeekNum(Today()-9))

NB: I'm not in front of a machine with Excel atm so the syntax may be a little bit out. You may also have to check the math re: Header rows and so forth, but that should be trivial.

-
Thanks for the suggestion. Is there any downside to installing this toolpack? – user1205197 Feb 16 '13 at 9:36
Only the memory cost of the extra functionality. It just, according to MS adds financial, statistical, and engineering analysis tools and functions. office.microsoft.com/en-au/excel-help/… – mcalex Feb 16 '13 at 9:41
If you're using Excel 2007 or higher, `WEEKNUM`is by default included. – Peter Albert Feb 16 '13 at 21:12
Using WEEKNUM could have several potential drawbacks, especially given how week numbers are defined (within WEEKNUM) at start and end of year , e.g. 2nd Jan can be in week 2, all years have a week 53 (and some a week 54) - and you need to make some assumptions about when the data is entered – barry houdini Feb 17 '13 at 10:12

You generally need to perform two steps:

1. Figure out the row you want to get. Following options

• `=COUNTA(\$A\$1:\$A\$52)` - will count all elements - but ignore blanks. So if somebody does not enter week 7 but week 8 & 9, this will return 8
• `=WEEKNUM(TODAY())` - returns the current week number. It will switch weeks immediately on Monday - even if no data is entered yet.
• `=MATCH("",\$A\$1:\$A\$52,0)` - this will return you the first row that is blank (so use it together with `-1`.
2. Once you have the row number (lets use `N` for the result/formula of step 1), you need to tell Excel to return the Nth row. For this, you have multiple options:

• `=INDIRECT("A"&N)` - this will construct the address, say A9 and then return the value in that cell
• `=OFFSET(\$A\$1,N-1)` - this will use A1 as the starting cell, offset it by N-1 rows and return this result
• `=INDEX(\$A\$1:\$A\$52,N)` - this will return you the Nth element of the range A1:A52.

In your case, each option is equally suited and a matter of personal taste. However, in case you are using a lot of these formulas in a complex model, the `INDEX` formula is superior to `OFFSET`and `INDIRECT` because it is non-volatile. This means that Excel only calculates it once - and afterwards only if any of its predecessors changes. `INDIRECT` and `OFFSET` on the other hand are volatile, so Excel will always calculate them - even if none of its predecessors changed - and as a consequence also all of the depending cells! This means that every recalculation will take much longer than with `INDEX`

Now you only need to combine step one and two into a joined formula, e.g. `=INDEX(\$A1:\$A\$52,WEEKNUM(TODAY()))` and you're done.

Barry's answer `=LOOKUP(9.9E+307,A1:A52)` is a great hack: it will always returns you the last value (from top to bottom) that is not blank.

-