dynamic range?

[eajustin15]

Greetings,

I am trying to create a dynamic lookup table that will account for all the cells in the column until it gets to zero. I have attached a file with an example of what i am trying to do. Basically I have Y/Y numbers in column D and there is data there unless there is not a retail unit in column B, so there displays a 0 if there is no data. In column A, i have a rank function that ranks Y/Y, my problem is I need the range to be dynamic so that it only includes the rows that have data in them and stops when it gets to 0. I know i can use the offset formula but i haven't got it yet. I also i know i need to name it a range so i can use it in my rank formula. anybody's help would be greatly appreciated. thank you.

[rylo]

Hi

Formulas, Define Name, Name: rankrng, Refers To: =OFFSET(Sheet1!$D$2,0,0,COUNTIF(Sheet1!$D:$D,">0"),1)
A2: =IF(D2=0,"",RANK(D2,rankrng,0))

HTH

rylo

[eajustin15]

thank you so much i think that did the trick, I do have another question. I may build on this and the data in column D will start to include negative numbers that I will want to include. How can I account for this? Also in the=OFFSET(Sheet1!$D$2,0,0,COUNTIF(Sheet1!$D:$D,">0"),1) i noticed you just reference the entire column of D. I may need to have it set to start at like D15 and end at a certain point. How can I change the range to account for this?
Thank you.

[rylo]

Hi

Change the formula for rankrng to be
=OFFSET(Sheet1!$D$2,0,0,SUMPRODUCT(--(ABS(Sheet1!$D$2:$D$15)>0)),1)

Now you can nominate your required range in column D, and it will cover both positive and negative numbers.

HTH

rylo

[eajustin15]

thank you so much i think that solves all my problems, Much appreciated

[eajustin15]

Hello, So i need to build on this a little more. I need the range to know to stop at the last number in the coloumn. So in this case i need it to stop at 14. Any suggestions.

[rylo]

Hi

Which number and which column? Why 14? Your example file has data, in one form or another, down to row 21.

rylo