Find last row with a numeric value in a range

[JCK101]

I'm trying to find the last row with a numeric value in a range (R8:X57).

I tried this: =MIN(ROW(R8:X57))+ROWS(R8:X57)-1

The result was 57 instead of 11 since the rows 12 to 57 have no values, but do have formulas.

I tried =LOOKUP(2,1/(LEN(R:X)>0),ROW(INDIRECT("1:65536"))) the result is 7 instead of 11, 7 is the heading row.

I tried this next one, it works, but it is too UGLY: =MAX(LOOKUP(2,1/(LEN(R:R)>0),ROW(INDIRECT("1:65536"))),LOOKUP(2,1/(LEN(S:S)>0),ROW(INDIRECT("1:65536"))), LOOKUP(2,1/(LEN(T:T)>0),ROW(INDIRECT("1:65536"))),LOOKUP(2,1/(LEN(U:U)>0),ROW(INDIRECT("1:65536"))),LOOKUP(2,1/(LEN(V:V)>0),ROW(INDIRECT("1:65536"))),LOOKUP(2,1/(LEN(W:W)>0),ROW(INDIRECT("1:65536"))),LOOKUP(2,1/(LEN(X:X)>0),ROW(INDIRECT("1:65536"))))

Any help will be appreciated.

Regards,
JC

 

[Rick Rothstein]

Here is one way to do it. This is an array-entered** formula...

=MAX(ISNUMBER(R8:X57)*ROW(R8:X57))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[Peter_SSs]

.. and if you were looking for a non array-entered formula, you could try

=AGGREGATE(14,6,ROW(R8:R57)/ISNUMBER(R8:X57),1)

 

[JCK101]

You read my mind
Thank you.

 

[JCK101]

Thank you Rick, yours works too, but I prefer the non-Array.

 

[JCK101]

Would this formula work if I moved the formula to another sheet? The data will stay in the original sheet.

If this is considered a new thread, I will start another one, just let me know.

 

[Peter_SSs]

Would this formula work if I moved the formula to another sheet? The data will stay in the original sheet.

Sure. If the original sheet is called, say 'Current Data' then the formula on another sheet would be:

=AGGREGATE(14,6,ROW('Current Data'!R8:R57)/ISNUMBER('Current Data'!R8:X57),1)

 

[JCK101]

=AGGREGATE(14,6,ROW('Current Data'!R8:R57)/ISNUMBER('CurrentData'!R8:X57),1)

Thank you [bold]very much[/bold] Peter for introducing me to Jeanie HTML!

Above formula worked like a charm.

I looked up what the numbers 14 and 6 mean, andI can use them in the future.

Also, what if we want to use the INDIRECT formulafrom a "LookupSheet" to lookup the same value on multiple data sheets one at a time?

e.g. The original data is in"DataSheet1", on the "LookupSheet" in Cell A2 I entered"DataSheet1".

Like this: =AGGREGATE(14,6,ROW(INDIRECT(A2&"!"&R8:R57,FALSE))/ISNUMBER(INDIRECT(A2&"!"&R8:X57)),1)

This did not work.
<!--[if !supportLineBreakNewLine]-->
Again thank you for all your help and time for making our lives a lot less distressful.

RJ

Excel Workbook
	A	B
1	Name of sheet to lookup	Max Row # in DataSheet1 (Hard coded) columns R--X
2	DataSheet1	5
3
4
5		Indirect function (False) sheet named in cell A2 (i.e., DataSheet1) columns R--X
6		#NUM!
7
8
9
10		Indirect function (True) sheet named in cell A2 (i.e., DataSheet1) columns R--X
11		#NUM!
12
LookupSheet

Excel Workbook
	Q	R	S	T	U	V	W	X	Y
1		ItemA	ItemB	ItemC	ItemD	ItemE	ItemF	ItemG
2					105	103	101
3					106	104	102
4					107
5					108
6
7
8
9
10
DataSheet1

Excel Workbook
	Q	R	S	T	U	V	W	X	Y
1		ItemA	ItemB	ItemC	ItemD	ItemE	ItemF	ItemG
2		201	203			209	211	214
3		202	204			210	212	215
4			205				213	216
5			206					217
6			207
7			208
8
9
10
DataSheet2

 

[Peter_SSs]

Also, what if we want to use the INDIRECT formulafrom a "LookupSheet" to lookup the same value on multiple data sheets one at a time?

e.g. The original data is in"DataSheet1", on the "LookupSheet" in Cell A2 I entered"DataSheet1".

You have the syntax slightly wrong and I would also include the ' around the sheet name just in case a sheet name ever includes any spaces.

=AGGREGATE(14,6,ROW(INDIRECT("'"&A2&"'!R2:R59"))/ISNUMBER(INDIRECT("'"&A2&"'!R2:X59")),1)

Thank you [bold]very much[/bold] Peter for introducing me to Jeanie HTML!

You are welcome.

BTW, the tags for bold should just contain B and /B not the whole word.

 

[JCK101]

Thank you again.