Data validation help please

Hi guys,

I have this issue and I really need an expert help.

I have this table

Data_Validation.jpg

C15 is a dropdown list of manufacturing lines (1, 2 and 3)

1) I need to create a dropdown list in C19 that shows all products made on a given manufacturing line based on my selection in C15 list. In other words, if I select 1 in C15, I should get in C19 a dropdown list that shows product 1, product 4 and product 7
2) I need to create a dropdown list in C21 that shows all products made on a given manufacturing line and of type A only, based on my selection in C15 list. In other words, if I select 1 in C15, I should get in C21 a dropdown list that shows product 4 and product 7

See if this what you want.

Please note that some range I've made as a fix. Thus, if your file is going to get bigger, then you'd need to make it dynamic.

However, for the 'Data Validation' drop down, the named range is dynamic.

Thanks so much guys for your contribution

Originally Posted by dluhut

See if this what you want.

Please note that some range I've made as a fix. Thus, if your file is going to get bigger, then you'd need to make it dynamic.

However, for the 'Data Validation' drop down, the named range is dynamic.

dlluhut,

Thanks so much. that's exactly what I was looking for. I just have one little problem. I am not good with pivot tables. the actual sheet I am working on has much more data than in the sample that I showed and I am not sure how to create pivot tables in the same way you did.

Is there any other way data validation can be done without creating pivot tables!
for example, offset formula. Something like this
OFFSET($A$2:$C$7,,$B$9-1) for C19. What do you think?

Originally Posted by Tony Valko

Post a small sample file that shows exactly what you have in the screencap and I'll fix it for you.

Thanks Tony, do you want me to attach the actual file?

Originally Posted by Tony Valko

No, not the actual file.

I'll just make up a sample file that matches your screencap.

What version of Excel does this have to work in? Your profile says you're using Excel 2020103.

I am using Excel 2016

So here is what the file looks like. the source table in one sheet
Table.jpg

The data validation is in another sheet
Data_validation.jpg
So basically, I have dropdown list in E12 for the manufacturing lines. The other three dropdown lists are dependent on my E12 selection
If is select 1 in E12, I should get:
1) dropdown list in M12 for the corresponding machine (there are total 8 lines and 8 machines)
2) dropdown list in E16 for the products manufactured on the selected line and type API only
3) dropdown list in M16 for the products manufactured on the selected line

I would really appreciate your help. I could not figure this out. I attached the spreadsheet

Originally Posted by mq1973

dlluhut,

Thanks so much. that's exactly what I was looking for. I just have one little problem. I am not good with pivot tables. the actual sheet I am working on has much more data than in the sample that I showed and I am not sure how to create pivot tables in the same way you did.

Is there any other way data validation can be done without creating pivot tables!
for example, offset formula. Something like this
OFFSET($A$2:$C$7,,$B$9-1) for C19. What do you think?

Hi mq1973,

I'm not an expert, but I believe others would be able to help out.

The reason why I'm using pivot tables is so that when your list gets bigger and bigger, you can just refresh the pivot tables and through the dynamic name range, it'll know automatically which range to take in order to get the 'list' for the data validation, which in a sense is dependent upon user's input.

Originally Posted by dluhut

Hi mq1973,

I'm not an expert, but I believe others would be able to help out.

The reason why I'm using pivot tables is so that when your list gets bigger and bigger, you can just refresh the pivot tables and through the dynamic name range, it'll know automatically which range to take in order to get the 'list' for the data validation, which in a sense is dependent upon user's input.

Thanks for your response. In this case, how would I apply pivot tables in the attached file. If you don't mind, would you please take a look at the file that I attached?

Originally Posted by mq1973

I am using Excel 2016

So here is what the file looks like. the source table in one sheet
Attachment 489614

The data validation is in another sheet
Attachment 489615
So basically, I have dropdown list in E12 for the manufacturing lines. The other three dropdown lists are dependent on my E12 selection
If is select 1 in E12, I should get:
1) dropdown list in M12 for the corresponding machine (there are total 8 lines and 8 machines)
2) dropdown list in E16 for the products manufactured on the selected line and type API only
3) dropdown list in M16 for the products manufactured on the selected line

I would really appreciate your help. I could not figure this out. I attached the spreadsheet

Hi mq1973,

Can you give an example of what E16 and M16 result would be?

For example, I don't understand what it means by 'Previous Product-API only' and 'Next Product'.

So far, what I can understand is that, when a user select a value form the data validation drop down list on cell E12, M12 would automatically display the result of the machine via the vlookup formula.

But what about the result (drop down validation list) for E16 and M16?

Thanks!

I've done it for you based on your request and your file setting.

However, I still think that it's 'cleaner' via pivot table etc. But it all comes down to personal preferences.

Thanks so much dluhut. I really appreciate it. You are probably right that pivot tables are better but I am problem is that I'm not good with pivot tables.

Originally Posted by Tony Valko

Sorry, I won't download files with macros/VBA code.

It's ok Tony and I really thank you for trying to help out.

Originally Posted by dflak

My apology for this "drive by" post, but I only have a couple of minutes. The data on the product data sheet needs to organized so you can make a pivot table out of it which means removing the blank rows between the data, and unmerging the cells in column B and duplicating the line numbers. For example Cells B2:B6 should contain 1.

I see that you have the data in separate tables. We might be able to do something with that instead if we use a naming convention for the tables such as Table_Line_1, Table_Line_2, etc.

Got to run now. I will take a look at this later.

Thanks dflak, I would appreciate this approach as well. This seems to be easier.

Well...like I said, it's preferences, but my post earlier also gave you the solution that you want by having formula (not pivot table) with dynamic name range.

So it should work like how you'd like it to be.

Hi dluhut,

when I select lines 6, 7 and 8 I get this. I guess this is because there is no API type for these lines

Dropdown.jpg

Originally Posted by dflak

Here is an approach that works with renaming the tables. I renamed them Table_1, Table_2, etc. and made the column lookup [Product Name]. I demonstrated the concept on Sheet1.

The validation uses INDIRECT: =INDIRECT("Table_"&A2&"[Product Name]") - I create the table name from which to look up from (how's that for ending the sentence with two prepositions?) from the value in column A. Column D is just there to double check my lookup.

I noticed that in Table_3, there are some blank table rows. These also show up in the drop-down list.

I think this looks easier. So how do I add another dropdown list for API type only?
Attachment 489782

Sorry. double post. I was trying to get rid of the attached image

Attached is the updated where the data validation shows nothing when there's no API Type for the machine chosed.

PS: If it's solved, remember to marked your thread as 'Solved'. You can do it via 'Thread Tools' and 'Solved' towards the top (beginning) of your thread/post

Originally Posted by dluhut

Attached is the updated where the data validation shows nothing when there's no API Type for the machine chosed.

PS: If it's solved, remember to marked your thread as 'Solved'. You can do it via 'Thread Tools' and 'Solved' towards the top (beginning) of your thread/post

Thanks so much dluhut for your effort. It is greatly appreciated.
The thread has been marked as "Solved"