Data Validation Question

[grewman]

I might be doing something very simple, but it just will not co-operate...

I need to set up a table as attached, with drop down lists, but the first column is the machine type, the 2nd column is the brand and the 3rd column is the model number.

Each of the 2nd and 3rd columns needs to be dependant on the previous column. eg if I select a certain type of machine in the 1st comumn, i just need the list of relevant brands to show up in comumn 2, and then when i get to comumn 3, just the possible model numbers for that particular brand of machine.

I can set up the columns to a certain point, but then I get stumped.

(I have attached the files, sheet 1 is the form i am trying to set up, sheet 4 is the breakdown of who makes what machine etc.)

Any help would be appreciated!!!!!


thanks,


Mark.

[Bob Phillips]

Try this

[grewman]

ahaaaaaa now thats what i spent hours trying to do!!!!!!!!!!!!!!

many many thanks for that...now if i look back at the codes, i can probably see where i went wrong!! I can not believe that you could do that so easily, when it took me foreverand still got no where( i knew in my head what i wanted it to do, but had no idea how to get it on the screen!!!!)


many thanks again,


Mark.

[Bob Phillips]

It was easy for me because someone in another forum asked a similar question last month. I built a solution then, and so I just copied it

[grewman]

thanks again..there are 2 other things that i need to add to that table, one of which i have done already..(the easy one...) which was add a value of poor, fair, good, and excellent in the far column.

The other thing i am hoping can be done, is to add a value in the functionality column. I need to show a value of 20 to 25 in each entry that is a vacuum. Is this an easy one too??



Thanks,

mark.

[Bob Phillips]

What determines if it is 20, 21, 22, 23, 24 or 25?

[grewman]

the value relates to nothing in the table at all...basically just a measure of how well the vacuum "sucks"...we hold a vacuum guage against the end of the pipe and read off the value.

[Bob Phillips]

I thiunk I see what you want.

You need to create a list of numbers 20-25 in some range, say T1:T6. Then in G11 add DV with a type of List and a formula of

=IF($B11="Vacuum",T1:T6)

It would be best to also change the worksheet code to clear this cell if the value in B is changed, as I do with columns C and D.

[grewman]

Have got the condition bit right, but cannot get a value in the funct boxes...


File attached...

I thiunk I see what you want.

You need to create a list of numbers 20-25 in some range, say T1:T6. Then in G11 add DV with a type of List and a formula of

=IF($B11="Vacuum",T1:T6)

It would be best to also change the worksheet code to clear this cell if the value in B is changed, as I do with columns C and D.

[Bob Phillips]

You have to use the name in the DV formula because the values are on another sheet, that is

=IF($B11="Vacuum",Functionality)

[grewman]

Once again,

Many thanks for all of your help.

I have tried using forums like this one before, but never seem to get a response...sonthis has been a GREAT change, and hope i can find more info here in the future too.


Thanks,

Mark.

[Bob Phillips]

I have tried using forums like this one before, but never seem to get a response...sonthis has been a GREAT change, and hope i can find more info here in the future too.

Really? All of the orums I have been on always seem very responsive. Bt this is the best

[grewman]

have to agree there, but I think part of the problem comes from me being in Australia, on the other side of the earth lol, and being online when you guys are all in bed asleep!!


With that table..how can i get the functionality box to change to a different colour eg yellow when i select Vacuum in the first column???

I have a bad habit of forgetting to put the value in as we only do that for a vacuum lol

Do you only work with excel, or the other MS products too??

[Aussiebear]

They don't all live on the other side of the earth....

[grewman]

They don't all live on the other side of the earth....

Sorry AussieBear....I was actually thinking about the other aussies when i typed that response...no hard feelings, and i have found a great forum with great feedback so far!!!

[Bob Phillips]

With that table..how can i get the functionality box to change to a different colour eg yellow when i select Vacuum in the first column???

I have a bad habit of forgetting to put the value in as we only do that for a vacuum lol

I would use conditional formatting, which would be very simple here, you could use cell value or Formula Is.

Do you only work with excel, or the other MS products too??

No I work with many other products. At the moment, I am mainly working with SQL Server, and trying to develop some decent skills in Visual Studio, VB.Net, C#, all in the BI framework.

[grewman]

[quote=xld]I would use conditional formatting, which would be very simple here, you could use cell value or Formula Is.




If you have a spare 5 mins, can you take a look at my possible mistake...I can get the first column to change colour, but i cannot get the funct column to change colour based on the "B" column showing Vacuum...


Sorry for keeping on hassling you,


Thanks,

Mark.

[Bob Phillips]

Your formula is all wrong

You have

="$B$11:$B$44=Vacuum"

The quotes should be around the product not everything.

You should refer to just one cell, not the whole range.

The row reference should be relative, so that you can copy it down.

So in G11, you should have

=$B11="Vacuum"

I don't understand what that red first condition is, it tests the same condition as far as I can see.