Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through and array with three possible decisions to make
I am probably making this more complex than I need to, so what is the
simplest way to loop through an array, looking for possibly three common bits of data and adding them together. Using a wine example... I am searching a series of 800+ records for: Wine Vendor Wine Origin Wine Category The user would choose from a drop down, either vendor, origin or category, and the macro calcs sales and costs based on the selections. For any of the three, the user can choose "ALL" or a specific value in each group. I started writing sub routines to take into account each possible option, but hoped there might be an easier way... can anyone help with a shorter solution? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through and array with three possible decisions to make
You can do this completely without programming. Say you have four columns:
Col A - Vendor Col B - Origin Col C - Catagory Col D - Sales Select a column header and: Data Filter AutoFilter You can then select combinations of options. Use the =SUBTOTAL() function to add up sales because SUBTOTAL() only sums the visible part of filtered data. -- Gary''s Student - gsnu200761 "jayklmno" wrote: I am probably making this more complex than I need to, so what is the simplest way to loop through an array, looking for possibly three common bits of data and adding them together. Using a wine example... I am searching a series of 800+ records for: Wine Vendor Wine Origin Wine Category The user would choose from a drop down, either vendor, origin or category, and the macro calcs sales and costs based on the selections. For any of the three, the user can choose "ALL" or a specific value in each group. I started writing sub routines to take into account each possible option, but hoped there might be an easier way... can anyone help with a shorter solution? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through and array with three possible decisions to mak
Unfortunately, I have been asked not to go that route for fear of confusing
individuals with too much information. I need to perform work on the data and just present the answer. I would have had it done with autofilters... "Gary''s Student" wrote: You can do this completely without programming. Say you have four columns: Col A - Vendor Col B - Origin Col C - Catagory Col D - Sales Select a column header and: Data Filter AutoFilter You can then select combinations of options. Use the =SUBTOTAL() function to add up sales because SUBTOTAL() only sums the visible part of filtered data. -- Gary''s Student - gsnu200761 "jayklmno" wrote: I am probably making this more complex than I need to, so what is the simplest way to loop through an array, looking for possibly three common bits of data and adding them together. Using a wine example... I am searching a series of 800+ records for: Wine Vendor Wine Origin Wine Category The user would choose from a drop down, either vendor, origin or category, and the macro calcs sales and costs based on the selections. For any of the three, the user can choose "ALL" or a specific value in each group. I started writing sub routines to take into account each possible option, but hoped there might be an easier way... can anyone help with a shorter solution? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex decisions, maybe? | Excel Worksheet Functions | |||
looping through an array of ranges | Excel Programming | |||
Looping data out of array | Excel Programming | |||
Simple Array Looping | Excel Programming | |||
Array / Looping Question | Excel Programming |