Trying to find the last non-empty cell in a specif

Question

I looked at a couple suggestions here and didn't find something that specifically worked for me. I would prefer not to use an Array Formula if possible. However, if that's the only way, I'll try to work with it.

I am working on an attendance workbook of sorts in Excel 2013. The workbook contains a Calculations sheet, a Lists sheet and a sheet for each month of the year. The Lists sheet has a list for names and a list for the months. I have included a link to a sample workbook to demonstrate how the sheets are setup and the basic functions, names, lists, etc that I am using.

My sample workbook: https://app.box.com/s/mgvums1vmnmnec7e7d0p4vi5daoyo5nv

When looking for the last non-empty cell in a specific row/range, I receive the correct result when I use the following LOOKUP on each specific Month sheet: (Which I found after some digging on a few sites) =LOOKUP(2,1/(C5:AG5<>""),C$4:AG$4) - For the Month of Feb, the last entry in the range C5:AG5 is in AA5, which is the 25th.

I also receive the correct result when I use the following Lookup from any non-Month sheet: =LOOKUP(2,1/(Feb!C5:AG5<>""),Feb!C$4:AG$4)

That being said, the two specific issues I am running into, are as follows:

1. I have been unable to work out how to come up with the last non-empty cell (which is the last marked day of attendance) when using Name and Month information selected in the Combo Box/Form Controls. Simply said, in my sample workbook, I want to know the last day in Feb that Johnny was in attendance.

I've tried starting simple, just using the "Month" I selected. I tried a few different itterations of the following: =LOOKUP(2,1/(Month&"!C5:AG5<>"""),Month&"!C$4:AG$4") The closest I've gotten was the above Lookup, which ends up turning into what you see below (when viewed in Calculation steps). It of course returns a #VALUE! error. =LOOKUP(2,1/"Feb!C5:AG5<>""",Month&"!C$4:AG$4")

I also tried this Array Formula and it gave me a result of 4. Which, isn't the proper result. And, I don't know where the result actually came from. =MAX((INDIRECT(F4&"!"&"C5:AG5")<>"")*(ROW(INDIRECT(F4&"!"&"C$4:AG$4"))))

2. I have been unable to work out how to come up with the last non-empty cell across all the Month sheets when using the selected Name from the Combo Box/Form Control. Basically, I want to know the last day of the year that Johnny was in attendance. In this sample workbook, that would fall on April 11th. I understand that the formula would just return "11". I believe I can format the result after the fact, using the resulting sheet name. (In this sample case, Apr)

I am fairly certain I will need to use a VLOOKUP. I just can't seem to break the barrier between the general concepts of what I want and the proper way of formulating it.

Any help would be appreciated. I'm feeling pretty lost at the moment. If you need more info from me, please let me know.

Thanks, Kurewe

Answer 1

The key is to use INDIRECT with the exact same LOOKUP formula you already had and use the MATCH formula to determine the row number for the selected name.

Place the following formulas:

• G3: =MATCH(F3,NameList,0)+4 (find the item number of the selected name within NameList)
• H2: =F4&"!C4:AG4" (construct the address for the date row for the selected month)
• H3: =F4&"!C"&G3&":AG"&G3 (construct the address to use for the selected name and month)
• E9: =INDEX(MonthList,ROW()-ROW(E$8)) copy down to E20 (find the name of the month for this row using MonthList)
• G9: =E9&"!C4:AG4" copy down to G20 (construct the address for the date row for the month in E9)
• H9: =E9&"!C"&G$3&":AG"&G$3 copy down to H20 (construct the address to use for the selected name and the month in E9)
• F9: =IFERROR(LOOKUP(2,1/(INDIRECT(H9)<>""),INDIRECT(G9)),"(n/a)") copy down to F20 (find the last day with an "X" in it using the addresses in G9 & H9)
• G4: =LOOKUP(2,1/ISNUMBER(F9:F20),ROW(F9:F20)-ROW(F8)) (find the index of the first cell with "(n/a)" in it)
• F5: =LOOKUP(2,1/(INDIRECT(H3)<>""),INDIRECT(H2)) (find the last day with an "X" in it for the addresses in H2 & H3)
• F6: =INDEX(MonthList,G4)&"-"&INDEX(F9:F20,G4) (construct the date of the last day of attendance within the year)