How To Program for Data Validation With If Functions and Defaults

**Freerider15** · 06-13-2011, 04:26 PM

So I'll try explaining this the best I can of what exactly it is I need...

Lets say I have my first Column with Data Validation giving me the choices A,B,C and D.

I then made a second column using IF functions within data validation so that for each choice A,B,C or D...they each have a seperate numeric list to choose from (1 to whatever #)...so you have something along the lines of 1-4 selectable with A, 1-2 selectable with B, 1-5 selectable with C, and 1-3 selectable with D.

Now heres where it gets tricky and I need some insight. I need the third column to be based off the first two columns with a default value that shows, but with a selectable list in case the default isn't whats desirable.

For example:

If I choose A in the first column, and 1 in the second column...the third column gives me a default value of W, but I want to be able to also select with a list in that cell between W,X,Y and Z as well.

Any thought?

I can give a little more clarification or go more in depth if needed.

-Thanks

**MarvinP** · 06-13-2011, 04:46 PM

Hi Freerider15 and welcome to the forum,

The third part of your question, to not show an error and allow any data to be input is shown on:
http://www.ssc.rdg.ac.uk/software/ex...Validation.pdf
Look at the Error Alert Tab and uncheck the first box.

For the first two parts of the question - will you allow VBA code to filter out the second boxes choices?

**Freerider15** · 06-13-2011, 05:10 PM

Originally Posted by MarvinP

Hi Freerider15 and welcome to the forum,

The third part of your question, to not show an error and allow any data to be input is shown on:
http://www.ssc.rdg.ac.uk/software/ex...Validation.pdf
Look at the Error Alert Tab and uncheck the first box.

For the first two parts of the question - will you allow VBA code to filter out the second boxes choices?

Marvin,

I have everything else working, except for getting the third column as I mentioned. I'm not getting any errors, I just don't know how to go about programming it into Excel. I will be very honest, I'm not very good with VB code (I'm a mech. engineer not computer engineer

).

**MarvinP** · 06-13-2011, 05:26 PM

Hi,

I don't think it takes any VBA programming. You need to find Data Validation and use it. See the link above to show you how.

Now - I'm assuming you don't have any VBA code in your sheet. Is that true or have you made it work using VBA code?

Attaching a sample workbook (or the real one) would help me understand the question. You can click on "Go Advanced" below the message area and then on the Paper Clip Icon above the message area to attach a file.

**Freerider15** · 06-13-2011, 06:10 PM

Originally Posted by MarvinP

Hi,
You need to find Data Validation and use it.

Now - I'm assuming you don't have any VBA code in your sheet. Is that true or have you made it work using VBA code?

Attaching a sample workbook (or the real one) would help me understand the question. You can click on "Go Advanced" below the message area and then on the Paper Clip Icon above the message area to attach a file.

I know what data validation is, I've already used it several times in my book.

I don't have any VBA code what so ever.

Went ahead and attached the file...

Where you get the output of M1, I need that to default to the value it does, but also allow for override through selection via a list with the values associated with the specific location.

**MarvinP** · 06-13-2011, 09:35 PM

Hi,

I've been wanting to build a sheet like this for a long time. The sheet hinges around doing Advanced Filters. On you sheet above you need to remove the blank columns to have a TABLE that excel can work with.

See the attached which demonstrates the fundamental ideas.

As you select Col A2 the Event Macro behind Sheet 1 fires and creates an advanced filter of the Data. Then when you pick B2 you will only see valid A2 numbers, based on your data. The advanced filter fires again when you pick a number which leaves choices for C2. If you don't select one of the choices for C2 it will add the data to the end of the data table.

This is the direction I was thinking to solve your problem. It does have VBA in it but the formulas go away. You should look at the VBA and read up on Advanced Filters.

If you need to take this into production, I'd create Dynamic Range Names for the DataTable and Advanced Filter Results. That way you can use those names in the Validation formulas.

I hope this shows another way to tackle your problem.