I need to include a list-driven data validation in a large number of cells,
which are all consecutive in a column. The items to populate the list reside
in both column B and column C on the same sheet. In any given row of the
range in which the items reside, column B or column C will be non-blank, but
not both. The way that I have been populating the list is to populate a
named range on another sheet (call it Sheet 2) by using whichever cell in a
given row has something in it, e.g.,

=IF(ISNUMBER('Sheet 1!B9),'Sheet 1!C9,'Sheet 1'!B9)

My boss doesn't like this idea, because there's the possibility that he will
add a row in the middle of the range on Sheet 1. I can get around this by
writing a macro to run when the worksheet opens to reconstruct the range on
Sheet 2 so that it will reference all rows in the range on Sheet 1, but that
won't address changes made during a session.

Is there a clever way to use a formula in the list definition for a Data
Validation to populate the list directly from the range in, say, B9 to C30,
using whichever column in a row happens to have something in it?