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 the valid datalist is already on another sheet I would like to be able to set the list source to the other sheet. I have tried to do this several times but the validation selection boxes seems to reject it 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 Lookup

The list of similar queries has given me the solution of naming the list. So thank you

Hi Ruth

There's two ways to do this:

1. Use Indirect as the list source (so in DataValidationAllowList
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 InsertNameDefine).

Then in DataValidationAllowList use:

=ValidList

I hope this helps!

Richard


On 27 Feb, 11:32, ruthhicks999
wrote:
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 the
valid datalist is already on another sheet I would like to be able to
set the list source to the other sheet. I have tried to do this
several times but the validation selection boxes seems to reject it
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 Lookup

--
ruthhicks999