data validation question

[joshua.wing]

All,
Thank you for taking the time to read this.

Here is what i am trying to do.

I have a dependency list that i am creating for work. I have two drop downs one is to select a system and the second it to select the actual test to be conducted on the system. The problem i am running into is i would like the second list to clear once i select a different system. I am also using the indirect statement in the second list.

once that is done i will have the time populate another section for the test times. I am thinking just a bunch of IF statements would work for that. any pointers on the second question would be great.

Thank you again!

Josh

 

[Smitty]

Welcome to the Board!

You could do that with a Change event, as they do respond to validation changes. It would probably be helpful because you'll need something in VBA if you don't want volatile time entries.

Here's some boilerplate change event code:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("xxx")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[joshua.wing]

Thank you for the reply. I am really trying to stay away from macros for the sheet. Is there a way to do it without using them? Again thank you

Josh

 

[Smitty]

Why stay away from macros if their use is logical?

For the Validation, I'm not aware of a formula means to clear the list, so I'd have to give that some thought. It doesn't mean it's not possible, just that it's never crossed my mind.

For entering static times you can use CTRL+:. There's no way via formulas to prevent times from updating, so your choices are manual entry or VBA.

 

[T. Valko]

Thank you for the reply. I am really trying to stay away from macros for the sheet. Is there a way to do it without using them? Again thank you

Josh

Is this what you're trying to do...

You select a system from drop down 1 and then a test from drop down 2. All is fine. However, if you change the selection in drop down 1 the current selection from drop down 2 is not a valid selection that relates to the selection in drop down 1.

For example,

Drop down 1, you select Baseball.
Drop down 2, you select Barry Bonds

Now, drop down 1 you select Football but Barry Bonds is still there!

Football and Barry Bonds are an invalid pair.

If you don't want to use VBA code there's not a whole lot you can do to REMOVE Barry Bonds from the cell.

You might be able to use conditional formatting to "hide" Barry Bonds but he'll still be there, you just won't see him.

Is what I've described what you're dealing with?

 

[joshua.wing]

Smitty,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
to be totally honest I am very weak in Basic, and I really don't want everyone that uses the spreadsheet to have to click the enable macros button. <o></o>
<o> </o>
<o> </o>
<o> </o>
Valko,<o></o>
<o> </o>
<o> </o>
<o> </o>
Exactly what I am trying to do is have it clear the "barry bonds" once I switch the first list. <o></o>
<o> </o>
<o> </o>
<o> </o>
I have the min and max times for each of the test to be conducted so I don't need it to formulate it. However I need it to auto populate once the top list selects the system and then each of the drop down lists are selected.<o></o>
<o> </o>
<o> </o>
<o> </o>
Thank you for helping out!<o></o>
<o> </o>
<o> </o>
<o> </o>
Josh<o></o>

 

[T. Valko]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
<o></o>
Valko,<o></o>
<o></o>
<o></o>
<o></o>
Exactly what I am trying to do is have it clear the "barry bonds" once I switch the first list. <o></o>
<o></o>
<o></o>
<o></o>

Ok, well, that can't be done without VBA code.

 

[joshua.wing]

I will dable in the formula that Smitty gave to correct the issue that i have.

Now i have a new one....

I have my list of min and max times for each but my problem is i need the times to populate 2 single cells (one for min and one for max). All this is going to be dependant on which test is selected. Here is my formula that i am using.

=IF(D2="9a1",AA55,IF(D2="9A2",AA56,IF(D2="9A3",AA57,IF(D2="9A4",AA58,IF(D2="10A1",AA60,IF(D2="10A2",AA61,IF(D2="10A3",AA62,IF(D2="10A4",AA63,""))))))))

My problem is i have more test times to populate but i have exceeded the max amout of IF statements. any suggestion? maybe indexing?

Thank you

Josh

 

[Smitty]

Take a look at VLOOKUP.

 

[joshua.wing]

vlookup worked perfect thank you!