Maybe you could use a dynamic name:

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

SteveinHouston wrote:

Hi folks,

I have 3 drop down lists on sheet 1 of a spreadsheet, all of which relate to
other sheets in the workbook. Can I set the drop down lists up to allow for
future expansion of the other worksheets. In Validate Data, i have tried
increasing the range of the source but this leaves a number of blanks at the
end of the drop down box. Although this solves the problem I was hoping for
some advice on a neater way to achieve my aims.

Thanks for the help.

--

Dave Peterson