Using Data Validation to populate from previous selection

**Bartos** · 06-29-2013, 08:51 PM

Hi,
I'm making a small spreadsheet in excel for calculating cutting speeds and RPM for cnc mill and let's say there are 4 variables and each is a sub group of previous as in:
Country
State
County
City
Now, I could type the parameters in manually according to a table with all the data that I need, but I'd like to make a "roll-down" list where I'd select parameter 1 and then according to this selection, list of variables in parameter 2 would change and so on.

Is it possible in excel? What is the function that I'm looking for - what is it called.

Thank you for your help.

Best Regards
Bart

**jeffreybrown** · 06-29-2013, 08:57 PM

Hello Bart & Welcome to the Forum,

Maybe I am reading your requirements wrong, but seems like this might fit...

If not, then a mock up sample sheet would help.

**Bartos** · 06-29-2013, 09:57 PM

Thank you very much for your QUICK answer.
I tried it with a simple table and it looks like it fits exactly to what I had in mind

and it works with numbers and formulas

.
I will play some more with it and if I have some more questions I'll post them here.
The following table is what I want to turn into "Data Validated" cells

image8.jpg
File taken from http://www.sandvik.coromant.com/en-g...s/default.aspx
and I believe it is their property.

Best Regards and again, Thank You
Bart

**Bartos** · 06-30-2013, 11:44 PM

I'm having a problem with data validation of merged cells.
When i select vertically merged cell (lets say a merge of 10 cells) then I have a selection of 1 filled line and 9 empty lines - as in cell K2 of the spreadsheet. can it be fixed?
Also im not sure whats the fastest way do get results with such a tables? Could you give me a hint, please. How would you do it? Im thinking of nesting Index function within Index a couple of times and also using MATCH but im not sure if it will work.

Best Regards
Bart

**martindwilson** · 07-01-2013, 08:21 AM

do not merge cells!

**Bartos** · 07-01-2013, 08:44 AM

It's not the hint I hoped for

but thank you. I'll try it today.

Best regards
Bart

**newdoverman** · 07-01-2013, 10:02 AM

To give you an idea of what you will require for your data validation, I have unmerged the cells and colour coded the basic materials of steel and aluminium and have shown possible Data Validation tables. This is only a start and not filled out because of missing data in the original table. Each of the sub tables produced will be assigned names to be used in the Data Validation.

I hope this is of use to get you going.

**Bartos** · 07-04-2013, 06:33 PM

newdoverman
thank you for your help
inspired by your post and some materials on the web, I;ve finished it - almost
I have one last glitch and i don't know why does it happen.
In cell G53 I've put the =MATCH function to look up a "position number" that i use in INDEKS function (cell G48 and G49) to get a proper region from KC_i_mc list.
Everything is working fine until position 13. (that is untill i select "St_32 HSS" in "Sub group"), anything farther down the list will result in region number=24.
What could be the reson?

Ok there are more problems. When I select
Material - Steel
Group - St_3
Sub Group - St_31

then for production process #1 and #2 everything's working fine but for #3 and #4 it doesn't work. It's all from one list and one function. What could be wrong?
When i change the value of production process to number 213 - it works properly and for value "abc" it doesn't. Why does excel go crazy with that?

Best Regards
Bart

**martindwilson** · 07-04-2013, 06:53 PM

match requires a last argument of 0 for exact match
=MATCH(G47,D45:D69,0)

http://office.microsoft.com/en-gb/ex...010062414.aspx

**newdoverman** · 07-04-2013, 08:12 PM

Martinwilson is right. If you insert a 0 for the last argument in the MATCH part of all the formulas in G49 to G53 wherever they are, your worksheet seems to work correctly.

G49 should be:

Formula:

Please Login or Register  to view this content.

G52 should be:

Formula:

Please Login or Register  to view this content.

G53 should be:

Formula:

Please Login or Register  to view this content.

You have made a lot of progress.

Good luck

**Bartos** · 07-04-2013, 09:47 PM

Thank you both for help, again

Best Regards
Bart

**krishnaiah** · 07-05-2013, 03:19 AM

hi,

U can use data validation in source use this functions indirect and counta formulas

Using Data Validation to populate from previous selection

LinkBack

Thread Tools

Rate This Thread

Display

Using Data Validation to populate from previous selection

Re: How is thes function called - what am I looking for :)

Re: How is thes function called - what am I looking for :)

Re: Using Data Validation to populate from previous selection

Re: Using Data Validation to populate from previous selection

Re: Using Data Validation to populate from previous selection

Re: Using Data Validation to populate from previous selection

Re: Using Data Validation to populate from previous selection

Re: Using Data Validation to populate from previous selection

Re: Using Data Validation to populate from previous selection

Re: Using Data Validation to populate from previous selection

Re: Using Data Validation to populate from previous selection

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions