Lists for List Validation

[ruthhicks999]

I am trying to use validation by list on a sheet in a workbook.

As the sheet it appears on is designed as a customer interface, and th
valid datalist is already on another sheet I would like to be able t
set the list source to the other sheet. I have tried to do thi
several times but the validation selection boxes seems to reject i
everytime I try to link to the other sheet.

Is there a way to set this please.
Sheet name Catologue data has the list of data in cells A2:A8
I want to use the list validation on a sheet called Customer Looku

 

[RichardSchollar]

Hi Ruth

There's two ways to do this:

1. Use Indirect as the list source (so in Data>Validation>Allow>List
use:

=INDRECT("'Customer Data'!A2:A8")

(note the single quotes before and after the sheet name)

or 2. Define a name (say ValidList) which refers to you validation
list on sheet Customer Data (ie via Insert>Name>Define).

Then in Data>Validation>Allow>List use:

=ValidList

I hope this helps!

Richard