Question re Data Validation in Excel

  oo7juk 12:26 30 Oct 2007


Is it possible for the source data to be entered on another worksheet?

I checked MS online and it stated it has to be on the same sheet as the data validation box.

Many thanks.

  xania 12:40 30 Oct 2007

Not sure exactly what you mean by this query, but it is always possible to work with data linked between worksheets or workbooks using the Paste Link option in Paste Special. All I will say is that, once you set up these links, don't amend the names or relative locations as this will break the link.

  VoG II 12:42 30 Oct 2007

It will work if you give your data validation list a name (Insert > Name > Define), then refer to that name on the second sheet.

  silverous 12:43 30 Oct 2007

There's your answer. It doesn't come much more credible than from MS.

I tried it, and sure enough if you try to refer to another sheet with the data validation you get an error.

The only way would be to have a formula perhaps on the same sheet which then looked up the relevant data on another sheet (to save duplication) OR use VBA.

  silverous 12:43 30 Oct 2007

Or do what Vog says which seems to work well :)

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Honor 9 Lite review

How Social Media has Propelled Political Graphic Design and Art in the Last Decade

The best kids apps for iPhone & iPad 2018

HomePod d’Apple : date de sortie, prix et fiche technique