Same dropdown/content in multiple sheets

[Polaron]

Hello all,

I need to solve the following without the use of macros:

I have a workbook with multiple sheets. Sheet1 is the budget sheet, Sheet2 is the Actuals sheet, and Sheet3 contains tables and reference info

On Sheet1, I have a working dropdown with Region information (East, West, Central, etc..) The data is located on Sheet3
On Sheet2, I want to use the very same dropdown with the very same content, but when I try to set up the dropdown via data validation, I get an error "This type of reference cannot be used in Data Validation formula"

Note: User may select a different region on sheet1 than on sheet2, but I do want them to have the same options.

Stumped. Appreciate any insights.

 

[mikerickson]

If you are using DropDowns from the Forms menu, you can't use off sheet references in the Input Range for a drop down.
The work-around is to use Named Ranges rather than explicit cell references for the Input Range.

i.e. rather than Input Range: A1:A5, create a named range Name:myList RefersTo: Sheet1!$A$1:$A5.

Then Input Range: myList will be accepted on any sheet.

 

[Michael M]

Are you trying to copy the DV list across from Sheet1 ??

Best way to do it is:
Highlight the cells in the list on sheet3 and make them a named range
Then use that named range in the DV source box
So, instead of say Sheet3!l1:L4....make it a named range called regions

 

[Polaron]

Are you trying to copy the DV list across from Sheet1 ??

Best way to do it is:
Highlight the cells in the list on sheet3 and make them a named range
Then use that named range in the DV source box
So, instead of say Sheet3!l1:L4....make it a named range called regions

Okay, i will give that a shot. Thanks very much!