Data Validation (cost centres)

[Laila1987]

Hi all,

I'm sure this question has been asked before but I am looking for some help with setting up data validation.

I have a list of business locations that have a range of allowable cost centres to be used against them.

For example i have purchase invoices in query logged on an excel workbook, one row per invoice. A site and cost centre and manually added for each invoice in separate columns. What i want to do is make the cost centre column a drop down containing allowable cost centre codes for that specific site location based on another worksheet containing that allowable cost centre data.

Invoice Num	Site	Cost Centre
123	York	01
345	Leeds	02
678	Manchester	01

<tbody>
</tbody>

The allowable cost centre data looks as follows,

Site	Name	01	02	03
YOR	York	Ok	Blocked	Ok
LEE	Leeds	Blocked	Ok	Ok
MAN	Manchester	Ok	Blocked	Ok

<tbody>
</tbody>

Any help or a point in the right direction would be appreciated. Thanks

 

[Mister H]

Hi and Welcome to the Board. What you need to do is search for Dependant List. Here is a link to a good site (OzGrid) that has a couple of samples you can download.

Dependent Validation. Excel Data Validation List Based on the Value of Another

Good Luck with your project

 

[James006]

Hi,

In order to get your dynamic data validation, it would be easier to amend the setup of your worksheet containing your cost center data ...

You can take a look at Debra's excellent solution ... :wink:

http://www.contextures.com/xlDataVal02.html#Dynamic

HTH

 

[Laila1987]

Worked a treat,many thanks both.

 

[Laila1987]

All worked fine accept no dependant drop down box will open for location code 'WAR' but works for all other sites. No one likes war, assume excel is no different ??

 

[James006]

Hi,

Just make sure 'WAR' is properly spelled ... with no leading or trailing spaces ... :wink: