2 Named Ranges For 1 Drop Down List

Hi,

I have 2 named ranges:

Labor_Code
Material_Code

Can these be combined for a data validation list?

Thanks,
bj

Hi,

I included the workbook to maybe clarify. The sheet Invoice Data has cells in red that I need to enter the codes from either the Labor sheet or the Materials sheet.

Thank you

As a workaround:
You would need a helper column to populate values from both ranges into 1 column & then use it as the source of the DV.
And to automate the populating process (when data in both ranges change) you can use 'Worksheet_Change' event, it means you'll need VBA.

Thank you I will appreciate any other options I want to keep this workbook free of VBA.

Hello Everyone,

I was able to figure this out using formulas and wanted to share. I created a table (Code List) on another sheet that has the 2 dynamic ranges into one column. I then used that table column as my data validation.

The formula for the table first cell then dragged down so there are no N/A's:

=IFERROR(LOOKUP(2,1/(COUNTIF($B$2:B2,Table1[Code])=0),Table1[Code]),LOOKUP(2,1/(COUNTIF($B$2:B2,Table2[Code])=0),Table2[Code]))

This is dynamic so if I add more Labor or Materials codes the Code List adds the new entries which in turn expands the data validation list.

I hope this may help others.