Dynamic field

[Tonyd789]

Hello all i'm hoping someone with the skills much greater than mine can figure this out for me.

I have a rather large spreadsheet and in column A i have an incriminating reference number going all the way down to row 2000, Column A has been used as a drop down list on another tab. But the list displays for all 2000 and currently the spreadsheet is filled in to around row 700ish.

What i would like is a dynamic field to look at column B and it if is not empty then the drop down list populated from column A will include from the top to where Column B is filled in.

I hope that makes sense?!

Regards T

 

[Tonyd789]

Not particularly, its my bosses spreadsheet and he has built it to fit 2000 rows of entries. Hence the reason why i wanted a dynamic range. Column B contains a formula that returns text open or closed depending on whats inputted. Column c also contains text in the for of a name selected through data validation drop down list.
I imagine in another row is left empty then my dynamic range is not going to show the last entry is it? Anyway to fix this. Deleting the empty rows is not an option either.

Regards Tony

 

[RoryA]

I'd suggest you use a table instead, since that will expand automatically as you add data, but in the meantime you could use:

=LOG!$A$3:INDEX(LOG!$A:$A,LOOKUP(REPT("Z",255),LOG!$B:$B,ROW(LOG!B:B)))

 

[Tonyd789]

Hi That seems to pull the full list of 2000 rows in.

 

[RoryA]

I guess the formulas go all the way down in column B then. Perhaps try:

=LOG!$A$3:INDEX(LOG!$A:$A,LOOKUP(2,1/(LOG!$B:$B<>""),ROW(LOG!B:B)))

 

[Tonyd789]

Sorry to be a pain, it is still pulling the full list of column A.

You are correct the column has formulas all the way to the bottom of the 2000 rows.

 

[RoryA]

What's the formula in B? That last one should only extend as far as the last cell in B that has something other than "" as the result.

 

[Tonyd789]

=IF(F713="","",IF(ER713="No","Open","Closed"))
this is the formula that is in B713.

Column C is a drop down list of set names if that makes it easier?

 

[RoryA]

I can't replicate the problem then. I just tested a sample sheet with that formula in column B and the defined name only went as far down column A as there were actual Open/Closed entries in column B.