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?

Jim,

I'm not sure what you mean by "reconstruct the range on Sheet 2," but if it builds your
formula in the inserted row(s) of Sheet 1, then perhaps you could have it run whenever a
change is made to the sheet, rather than just when the workbook is opened. In the Sheet
module for the Sheet 1, use the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
' your code here
End Sub

The routine will run whenever any change is made to the sheet, but the overhead won't likely
be noticeable. If it is, we'll find a way to have it run on a more selective basis.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
"Jim Skrydlak" wrote in message
...
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?

That will do it. I'd forgotten about the Worksheet Change event. Thank you
for reminding me!

Jim

"Earl Kiosterud" wrote:

Jim,

I'm not sure what you mean by "reconstruct the range on Sheet 2," but if it builds your
formula in the inserted row(s) of Sheet 1, then perhaps you could have it run whenever a
change is made to the sheet, rather than just when the workbook is opened. In the Sheet
module for the Sheet 1, use the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
' your code here
End Sub

The routine will run whenever any change is made to the sheet, but the overhead won't likely
be noticeable. If it is, we'll find a way to have it run on a more selective basis.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
"Jim Skrydlak" wrote in message
...
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?