Data Validation Using both List and Custom options

[C5X2C]

Greetings!

I could use some Excel 2003 help.

I think I would like to incorporate both Validation options (List and Custom). Here's the scenario:

Processing Mode can be either Shared of Dedicated

Unless Processing Mode is Shared (R17), then there should be no Shared Pool entry (S17).
Validation Custom Formula: =R17 = "Shared" (and a Error Alert).

If Processing Mode is Shared, the I'd like a drop down list to force the user to choose a Pool name.
Validation List Source: =Shared_Pool

<TABLE style="WIDTH: 147pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=196 x:str><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 75pt; mso-width-source: userset; mso-width-alt: 3657" width=100><TBODY><TR style="HEIGHT: 23.25pt; mso-height-source: userset" height=31><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff; WIDTH: 72pt; HEIGHT: 23.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28 height=31 width=96>Processing
Mode</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #99cc00; WIDTH: 75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=100>Shared Pool (P6/P7) </TD></TR><TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: #e0dfe3" class=xl26 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND: #ffff99; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid; mso-ignore: style; mso-pattern: auto thin-diag-cross" class=xl25> </TD></TR></TBODY></TABLE>

Using both Validation schemes doesn't seem to be an Excel capability, but I would think there still is a way of doing it. My VB is not strong.
I would appreciate any help on this matter, I've been looking at it for a while and have hit the wall! Thanks

 

[Marcelo Branco]

Maybe this

Name the list of allowed Pools as Shared. To do this
Select the list
in the Name box, beside the formula-bar, type Shared and hit Enter

Then in S17
Data > Data Validation > List

formula
=INDIRECT(R17)

HTH

M.

 

[C5X2C]

I have already named the list Shared_Pool.

I am using the validation list in S17. I'm not sure where you want me to put the formula =INDIRECT(R17) or what that formula is doing.

 

[mikerickson]

Is Shared_Pool a dependent name? e.g.
Name: Shared_Pool
RefersTo: =IF(Sheet1!$R$17="shared",some range, " ")

 

[C5X2C]

I think is just a standard list that I refer to for the drop down selections, don't know about Dependent names?

 

[mikerickson]

http://www.contextures.com/xldataval02.html

 

[C5X2C]

Thanks for the reference, I will bone up and regroup!

 

[Marcelo Branco]

I have already named the list Shared_Pool.

I am using the validation list in S17. I'm not sure where you want me to put the formula =INDIRECT(R17) or what that formula is doing.

What i was trying to say is:
rename your list as Shared (exactly the value in R17 that allows entries in S17)

Then in S17
Data > Data Validation > List
and insert this formula
=INDIRECT(R17)

M.

 

[C5X2C]

Thank you for the assistance. I understand how the INDIRECT function works, passing a list name dependent on the selction made in another cell. After renaming some lists, I'm good to go. Thanks again!

 

[Marcelo Branco]

Thank you for the assistance. I understand how the INDIRECT function works, passing a list name dependent on the selction made in another cell. After renaming some lists, I'm good to go. Thanks again!

You are welcome and tks for the feedback

M.