Input data to specific cell based on multiple dropdowns

**cpercival** · 02-18-2011, 02:53 PM

This seems like it should be possible, but I can't figure it out.
My vision:
1) User clicks on "Enter Data" button
2) Button opens user form or hyperlinks to a hidden worksheet

3) User selects choice from a dropdown narrowing selection to a range of rows
4) User selects choice from a second dropdown further narrowing selection to 2 rows
5) User selects choice from a third dropdown further narrowing selection to one row
6) User selects choice from a fourth dropdown which defines the column and thus the specific cell
7) User enters data into a textbox which is then added to the cell defined by the previous dropdown selections

I have it designed, but can't figure out the programming. I'm pretty new to VBA and am trying to learn as I go, but I am stumped. Probably not the easiest first project, but it is what it is.

First, can Excel do this or would something else work better? Any help would be appreciated.

**6StringJazzer** · 02-19-2011, 10:29 AM

It's certainly possible. Since you have designed it, can you share what you have designed? It's hard to give a concrete answer the way you have described it.

If you're allowing the user to see rows on a worksheet one possibility is to use the user's selection to do AutoFiltering, but to give a specific solution I would want to see more detail about how the data is laid out and what kind of choices you want the user to make.

**cpercival** · 02-22-2011, 01:00 PM

Sorry it took me a while to respond. I've attached the file (hopefully it works). On the "Zone 4" sheet, when the user enters data via the userform, that data should be added to the value in the correct "Actual" cell according to comboboxes for part number (1-4), machine, part type, and day. Let me know if more info is needed.

**6StringJazzer** · 02-27-2011, 11:05 PM

You are prompting for part number. I can't tell from your data what is a part number, or what is the list of part numbers they should be able to choose from. Same comment for part type and machine. No data is labeled as part number, part type, or machine.

Once I can understand how you want this to work, I think the coding part is going to be pretty easy.

**cpercival** · 02-28-2011, 09:11 AM

Sorry, should have thought of that. Column A is the part number, B is machine type, C is part type. You can ignore the "Master" sheet. Thanks again for taking a crack at this.

**6StringJazzer** · 02-28-2011, 09:47 AM

I dug down a little deeper and did figure that out.

I changed how your comboboxes are populated. I created new lists on Sheet3 so there are not blank rows in between the values. They are named ranges. However, if you add or remove values in those lists, you will have to update the named ranges accordingly. You would also have to update the calculation that determines the cell based on the three choices the user made.

After re-reading your original post, I would comment that you are not gradually selecting down. None of the choices is dependent on any of the others. The interface allows the user to select four parameters (in any order), and specify a quantity. I started out by narrowing them down for each choice but realized that isn't necessary.

Hopefully you will find this helpful. I did some basic testing but not exhaustive, so I suggest you test this thoroughly.

**cpercival** · 02-28-2011, 10:13 AM

I think you did it! I'll play around more, but initially it looks great. I'm going to try to dig into your code and try to figure out how you did it. Thank you very much!