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

 

[RoryA]

Define a name that refers to:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$B:$B))

changing the sheet name as required, then use that name as the source for the data validation.

 

[Tonyd789]

Hi Thanks for the fast response its not working this is the formula i have put in

=LOG!$A$3:INDEX(LOG!$A:$A,COUNTA(LOG!$B:$B))

I what it to stat from row 3, The defined name is Ref_Num however drop down list pulls in all populated cells in column A.

Any further advice?

 

[RoryA]

Then I suspect the cells in column B are not actually empty. Do they contain formulas, or literal values? (I am assuming you updated the data validation to use the Ref_Num name, rather than whatever it was using before)

 

[Tonyd789]

Your suspicions were right i have changed the column to C which is textmy list works now however goes to 696 where the log is filled into 709. Any idea on this?

 

[Tonyd789]

Sussed it had to add +13, not sure why though.

 

[RoryA]

Do you have blank cells within the data in column B?

 

[Tonyd789]

I have several rows throughout the 796 rows that are no longer populated with info, the earliest one is at 300 odd. Could that be the problem??

 

[RoryA]

Yes, COUNTA would ignore blank cells, so the count would be off. Does column B contain text or numbers? Or both?

 

[RoryA]

Also, is there a reason for having all that unused data in column A?