I need some advice/recommendations for tweaking/streamlining a data entry form.
First, please see attached (working) data entry form. Allow me to preference that this particular version is generic... the actual data entry form will contain additional fields.
Anyhow, here's the current process:
- User opens XLS and selects values from the 3 drop-down boxes.
- Upon clicking "Ok", the previously selected values are inserted in cell range B1:D1.
- I've labeled the drop-down row as "Scenario #1"
Ok, here's where I need some help/ideas:
- Upon opening the form, the user will select "# of scenarios" from a drop-down menu. The value for # of scenarios might range up to 10 scenarios.. or possibly even beyond 10.
- For sake of argument, let's say that the user selects a scenario value = 6.
- If so, the user then must be able to enter 6 values for each of the 3 fields/drop-downs.
- Consequently, the worksheet will store data from B1:D6.
Now, I guess I could just add drop-downs for up to 10 scenarios. Problem is though I have more than just 3 fields for each scenario. Thus, the pop-up form could get quite cluttered.
Also, what if user selected "15 scenarios"... so, you can see that this approach may not be the best option.
Next, it was also requested to use Excel (vs. Access where this could be more easily accomplished).
Given the Excel constraints, does anyone know of a somewhat "dynamic" solution for this particular method. I'm open to ideas (would love to see an actual solution in XLS).
Thank you in advance,