Dynamic Range - probably really easy fix!

[justanotheruser]

Hi all,

I've got a dynamic named range called Products that refers to

=OFFSET(Prices!$A$2,0,0,MATCH("*",Prices!$A:$A,-1),1)

In the sheet called Prices, I have the following:

Excel Workbook
	A
1	Products
2	Product 1
3	Product 2
4	Product 3
5	Product 4
6	*
7	*
8	*
9	*
Prices

However, with this dynamic range, cells A2, A3, A4, A5 and A6 are selected - even though A6 is a blank cell. Could someone help me fix the formula for the dynamic range?

Thanks in advance for your help! :D

 

[AlphaFrog]

Try this...

=OFFSET(Prices!$A$2,0,0,MATCH("*",Prices!$A:$A,-1)[COLOR="Red"]-1[/COLOR],1)

 

[justanotheruser]

Great stuff! Works perfectly, thanks What does the extra -1 mean though - one row less?

 

[AlphaFrog]

Great stuff! Works perfectly, thanks What does the extra -1 mean though - one row less?

In this case, Yes.

 

[justanotheruser]

Okay great, thanks again for your help!