..Hi,

I am trying to use a drop down list and have checked the "ignore blank"
option but it does not ignore the blank cells. So I did the following to try
and cricumvent it but need some help.

Column M contains the list
Column N I put an if statment to set the cell's to 1 if they are not blank
Column O I used the index to list only the cell's that have something in it.
Column P I did an iserror & vlookup to create a list of only "non-blank" cells
N4 I used the Countif statement to see how many rows have info. Curretnly
n4=48

I would like to use that number to set the end of the list I am trying to do
the following:
List = p$6:p$(6+n4)----would like it to read p$6:p$54

Thanks,

Ben

Try:


=OFFSET($P$6,0,0,N4,1)

"Ben Dummar" wrote:

.Hi,

I am trying to use a drop down list and have checked the "ignore blank"
option but it does not ignore the blank cells. So I did the following to try
and cricumvent it but need some help.

Column M contains the list
Column N I put an if statment to set the cell's to 1 if they are not blank
Column O I used the index to list only the cell's that have something in it.
Column P I did an iserror & vlookup to create a list of only "non-blank" cells
N4 I used the Countif statement to see how many rows have info. Curretnly
n4=48

I would like to use that number to set the end of the list I am trying to do
the following:
List = p$6:p$(6+n4)----would like it to read p$6:p$54

Thanks,

Ben