Data>Validation>List .. How do I calculate the last ro

[jhweaver]

I have a a cell that only allows an input from a pre-defined list using the Validation option where 'Allow' = List and 'Source' = D2:D11.

However, occassionally I need to add (or delete) options from the list in column D so the range will change, eg. D2:D14.

How can I put that into the 'Source' field so that it will automatically calculate the last row of culmn D? Or is that beyond the capabilities of Validation? If so, can anyone recommend any other solutions?

 

[shades]

Re: Data>Validation>List .. How do I calculate the las

Welcome to the board!

I have a a cell that only allows an input from a pre-defined list using the Validation option where 'Allow' = List and 'Source' = D2:D11.

However, occassionally I need to add (or delete) options from the list in column D so the range will change, eg. D2:D14.

How can I put that into the 'Source' field so that it will automatically calculate the last row of culmn D? Or is that beyond the capabilities of Validation? If so, can anyone recommend any other solutions?

Define the range in D as a dynamic range which will automatically adjust, then use that defined name in data valdiation.

Go to Insert > Name > Define. In the box at the top put your dynamic name (i.e. myList). In the Refers to box at the bottom put this:

=OFFSET(Sheet1!$D$2,0,0,COUNTA(Sheet1!$D:$D),1)

Click Add. Then OK.

Then in Data validation, for List put =myList

 

[jhweaver]

Re: Data>Validation>List .. How do I calculate the las

Thanks Shades. That works a treat