Same dropdown/content in multiple sheets

Polaron

New Member
Joined
Apr 3, 2014
Messages
16
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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,971
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,052
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

New Member
Joined
Apr 3, 2014
Messages
16
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,809
Messages
5,627,019
Members
416,215
Latest member
Ostie3994

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top