OK so I have a worksheet with a drop-down list that has the states of the US
listed. In the next column are 50 dependant drop down lists that have various
cities listed, and depending on which state you choose, the drop down list of
that state's cities shows.

Is there any way I can include the option "Other" in the cities column (call
it column B) without going back to my list source (my list is a named range)
and typing in "other" in the lists of each of the 50 states. I am a little
too lazy to do all that if I don't have to.

Thank you for your time

being lazy also, I would probably insert a row of cells just below the first
row of cities
copy the first row and paste into this second row
then in the first Cell enter "Other" and copy across the 50 columns.
this will have the Other as the first entry in the column.

If you have an equal number of cities in each column, you could do
something similar to put them with Other as the bottom entry

If you have your cities sorted alphbetically and want to have the "other" as
the bottom one
use ZZZ as your entry, do a sort on each column and use a global
find ZZZ replace with Other


"BB" wrote:

OK so I have a worksheet with a drop-down list that has the states of the US
listed. In the next column are 50 dependant drop down lists that have various
cities listed, and depending on which state you choose, the drop down list of
that state's cities shows.

Is there any way I can include the option "Other" in the cities column (call
it column B) without going back to my list source (my list is a named range)
and typing in "other" in the lists of each of the 50 states. I am a little
too lazy to do all that if I don't have to.

Thank you for your time

Thanks for the tips!

Problem is, I wish I was that organized. But the data I am talking about is
far less organized. I got the download off of a IRS publication, and it looks
something like this.

stateX cityY 52
stateX cityZ 87
stateX cityYZ 14
stateQ cityP 13
stateQ cityP 54


In other words, i only have a handful of columns, one each for state, city,
and data, which happens to be per diem rates for travel expenses.

It was hell going through and highlighting & naming all the ranges by
looking at the state to the left, but that was the fastest way to do it.

Now in order to insert "Other", I would have to insert a cell between each
of the different states, I can't just copy & paste across the board.

I know it sounds like lazy whining, and I will probably end up having to do
it exactly that way, by going to each and every state list and adding an
other, but I just want to make sure there are no formulas I can type into the
data validation list source to make the source both the name range and the
word "other."

And by the time it took me to write up this response, I probably could have
finished it the long way...

"bj" wrote:

being lazy also, I would probably insert a row of cells just below the first
row of cities
copy the first row and paste into this second row
then in the first Cell enter "Other" and copy across the 50 columns.
this will have the Other as the first entry in the column.

If you have an equal number of cities in each column, you could do
something similar to put them with Other as the bottom entry

If you have your cities sorted alphbetically and want to have the "other" as
the bottom one
use ZZZ as your entry, do a sort on each column and use a global
find ZZZ replace with Other


"BB" wrote:

OK so I have a worksheet with a drop-down list that has the states of the US
listed. In the next column are 50 dependant drop down lists that have various
cities listed, and depending on which state you choose, the drop down list of
that state's cities shows.

Is there any way I can include the option "Other" in the cities column (call
it column B) without going back to my list source (my list is a named range)
and typing in "other" in the lists of each of the 50 states. I am a little
too lazy to do all that if I don't have to.

Thank you for your time