Help with data validation please

swst

Board Regular
Joined
Jan 17, 2005
Messages
186
Is it possible to set up data validation lists based on information on other sheet tabs within a file or do they have to be on the same sheet
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
swst,

To my knowledge the "information" must be on the same sheet.
But you can make a "permanent copy" of the cell you want to refer to.

example you want to refer to cell A1 from Sheet1, your validation being on sheet2
just type in a cell sheet2 something like: =Sheet1!$A$1

kind regards,
Erik
 

swst

Board Regular
Joined
Jan 17, 2005
Messages
186
I am looking to record stock receipts for multiple stock items
I need to validate against a list of items eg stock codes to ensure correct entry
I dont want to have to set up drop down lists for every row

Any thoughts
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
if you send me an email, I'll reply with an excelfile (just made) in attachment.

kind regards,
Erik
 

Mark F

Well-known Member
Joined
Jun 7, 2002
Messages
500
Just a thought

I think I am right in saying that you are able to use a named range from any sheet in the workbook - it doesn't need to be on the same sheet if it is named

If you name the stock list range on Sheet 2 (say "stocklist"), in data validation on Sheet 1, drop down to list and type "=stocklist" as the range source.

Mark
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Mark,

You're absolutely right!
I always tried to select the range, and you can't go to another sheet whan you're in the validation-formula-selecting-box (or how do you call that one).

Sorry, swst, to have "helped" you with my detour.

kind regards,
Erik
 

Forum statistics

Threads
1,147,669
Messages
5,742,520
Members
423,736
Latest member
dracula cyrus

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