Formula too complex

**Aland2929** · 10-13-2011, 11:10 AM

I have a userform from which I select a customer. Cells update from VLOOKUP formulas as I select different customers. I have two questions: 1. Is there a more efficient formula than the one I am using as there will be more than two months of data. 2. I would like the formulas to be in VBA (not in the worksheet) and update the worksheet when I click a different customer. I have attached a sample file.
Thank you in advance.

**Aland2929** · 10-13-2011, 01:20 PM

I have included the date in the userform but would still like to know how to simply the formula or if I transfer the formula to VBA, how do I create a change event in the userform?
Thanks in advance.

**JBeaucaire** · 10-13-2011, 01:43 PM

Since you're using the worksheet itself anyway to display your report, there's no real benefit to the popup userform, in my opinion, it's just more plumbing.

Here's the same functionality (in VBA) but with no userform. Select a Month and a Customer using the drop downs and the values from those sheets will be displayed without visible formulas. If you select a month that doesn't exist yet, you'll simply get blank cells.

You can hide the K:L lists if for appearance. I prefer the customer list on like this because you may have customers in September you did not have in August.

The lists in K:L are dynamic, if you add/delete from the list (no blanks, please) then the drop downs will adjust themselves for you.