Grab number from column but not the last number

[kdrennan]

Hello All,

I have a workbook with multiple worksheets. I populate the main page with information from the other worksheets.
This has worked fine until I tried to grab a number (not the last number) from column that has a total at the bottom.

I want to populate cell J3 on the main page with the last entered number of column AD on sheet 8. column AD has a total at the bottom that I do not want. I just want the last number in the column above the total. there are a number of empty cells in-between but all are empty.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I have assumed data will not go below row 1000 in column AD. You can adjust that number up or down if you want.
See if this does what you want.

=LOOKUP(9.99E+307,AD1:INDEX(AD:AD,LOOKUP(9.99E+307,AD1:AD1000,ROW(AD1:AD1000))-1))

 

[Peter_SSs]

If you have Microsoft 365, you could also try

=INDEX(AD1:AD1000,LARGE(FILTER(ROW(AD1:AD1000),AD1:AD1000<>""),2))

 

[kdrennan]

If you have Microsoft 365, you could also try

=INDEX(AD1:AD1000,LARGE(FILTER(ROW(AD1:AD1000),AD1:AD1000<>""),2))

I do have 365, this is not pointing to the sheet8? is that why it gives me a NA error

 

[kdrennan]

I would like to use the code that works on both 2019 and 365 though.. Where can I insert sheet name at?

 

[kdrennan]

I GOT IT, THANK YOU VERY MUCH

 

[Peter_SSs]

You're welcome. Glad you got it sorted.