I have a column that I place check marks in if a certain condition is met. Some cells are checked and some are empty. I need to find the row number of the last checked cell. There will be only check marks or empty cells in in this column. And thanks again for all the wonderful help in the past.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Sorry, I should have mentioned that I need to do this with a worksheet formula. I have more than one column that I need to do this with. I am trying to find how many days since last check mark. I have a formula that works for the dating part but need to find the row number.
Upvote
0
Fluff
MrExcel MVP, Moderator
- Joined
- Jun 12, 2014
- Messages
- 92,540
- Office Version
- 365
- Platform
- Windows
In that case have a look here https://exceljet.net/formula/last-row-number-in-range
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
=MATCH(REPT("z",255),A:A)
yields the native row number of the last character in column A, even that's a blank.
yields the native row number of the last character in column A, even that's a blank.
Upvote
0
Maybe I didn't explain myself well. None of the above seem to work for what I need. I needed the row number of the last row in a single column that had a check mark in it. After more research I found this that works exactly as needed.
=SUMPRODUCT(MAX((B6:B41<>"")*ROW(B6:B41)))
As used in the complete formula:
=IF(SUMPRODUCT(MAX((B6:B41<>"")*ROW(B6:B41)))>0,DATEDIF(INDIRECT("A"&SUMPRODUCT(MAX((B6:B41<>"")*ROW(B6:B41)))),TODAY(),"D"),"")
The other formulas either gave me the first row number which was empty or the very last row in the range. I want to thank all for trying. Sorry I didn't explain it better.
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
=MATCH(REPT("z",255),B6:B41)
will give you relative row number.
=MATCH(REPT("z",255),B:B)
will give you native row number.
This idiom is very fast.
will give you relative row number.
=MATCH(REPT("z",255),B:B)
will give you native row number.
This idiom is very fast.
Last edited:
Upvote
0
Thank you again. I can see where that would be faster. It works great except my formula is in the same column and I get a circular reference error using the MATCH/REPT formula you provided. I have dates in column "A" and I check off the tasks performed on that day in the next 7 columns with the fomula at the bottom letting me know how many days since it was performed last. The SUMPRODUCT formula is working fast enough for me at this point. Again thanks for the solution. I may use a helper column with the MATCH/REPT formula to make my formula simpler.
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Which formula did you try and where have you implemented that formula?
Upvote
0
I used this:
=IF(ISERROR(DATEDIF(INDIRECT("A"&MATCH(REPT("z",255),B:B)),TODAY(),"D")),"",DATEDIF(INDIRECT("A"&MATCH(REPT("z",255),B:B)),TODAY(),"D"))
In cell "B43". Column "A" has the dates the tasks in columns "B" thru "H" have the check marks in. And I might mention it works fine if I put it in any other column.
[TABLE="width: 599"]
<tbody>[TR]
[TD="class: xl63, width: 65, bgcolor: green, align: center"]Date[/TD]
[TD="class: xl63, width: 65, bgcolor: green, align: center"]Mow[/TD]
[TD="class: xl63, width: 65, bgcolor: green, align: center"]Edge[/TD]
[TD="class: xl63, width: 65, bgcolor: green, align: center"]Weeds[/TD]
[TD="class: xl63, width: 65, bgcolor: green, align: center"]Leaves[/TD]
[TD="class: xl63, width: 65, bgcolor: green, align: center"]2, 4D[/TD]
[TD="class: xl63, width: 65, bgcolor: green, align: center"]Roundup[/TD]
[TD="class: xl63, width: 72, bgcolor: green, align: center"]Weed & Feed[/TD]
[TD="class: xl63, width: 72, bgcolor: green, align: center"]Seeded[/TD]
[/TR]
</tbody>[/TABLE]
Row 43 Has the days from last checked.
This formula is now in Cell "B43" and seems to be working fine:
=IF(SUMPRODUCT(MAX((B6:B41<>"")*ROW(B6:B41)))>0,DATEDIF(INDIRECT("A"&SUMPRODUCT(MAX((B6:B41<>"")*ROW(B6:B41)))),TODAY(),"D"),"")
Hope this explains it better.
Last edited:
Upvote
0
