Sanjay has attended:
Excel Advanced course
Dynamic cell range
We covered data validation where I can select values from a set list. My question is where I increase the values in the list, how can be drop down menu be automatically updated to include the new input? Suppose the items on my list in data validation are from cells C2 to C10 and I now increase the list by adding a value in cell C11. How would the drop down menu get updated?
RE: Dynamic cell range
Hi Sanjay
Thanks for your question
You should create a named range, by clicking on Insert-Name_Define. Give it a suitable name, and then in the refers to box enter the following formula
=OFFSET($D$1,0,0,COUNTA($D:$D),1)
The offset function starts in column D, and uses the countA function to determine how many rows hold data.
Regards
Stephen
