MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to put Data Validation List in a different worksheet?

  Asked By: Steven    Date: Jan 04    Category: MS Office    Views: 1798

From Data/Validation, in sheet1 I can specify LIST and its source, say
=$c$1:$c$20. It worked perfectly. If I try:
=sheet3!$c$1:sheet3!$c$20, Excel 2003 told me that I was not supposed
to do that!

Putting the validation list separately has many merits. one is to avoid
accidentally modifying the List while we are manupulating worksheet
data such as deleting 5 rows, copy this column and paste that row. The
other reason is after a couple of months, we may forget that these
are 'data validation list' and we may ponder why we have this cells co-
exist with all other sales figures in a yearly report. They seemed so
starnge to sit side by side to each other. So, I want the list to be
placed in a different sheet!



2 Answers Found

Answer #1    Answered By: Wanda Patterson     Answered On: Jan 04

Just name the =sheet3!$c$1:sheet3!$c$20 range and you can use that
named range in the validation  list. I do this all the time. Why they
would allow it to work as a named range but not as a hard-coded range
is beyond me.

Answer #2    Answered By: Joel Brown     Answered On: Jan 04

That's not the right format for a range on another sheet. It's


Didn't find what you were looking for? Find more on How to put Data Validation List in a different worksheet? Or get search suggestion and latest updates.