I am trying to create a formula that I am afraid is beyond the scope of excel. My first question is - Is it beyond the scope? and second - Do you have any suggestions as to what to use for what I am doing?(ie. crystal reports, jreports, etc.)
I have a worksheet that has multiple fields to track sales activity. I am trying to create reports that follow 3 main criteria but that material is variable. I have inserted an additional worksheet that has the 3 criteria fields as drop downs. Depending on what I select as the drop down is how I would like to create the results of the data.
So the 3 drop downs are "Salesperson", "Type of Sale", "Source". It is set up for an insurance business - just info to add clarity to my example.
The data that I am trying to compile works like this. If no drop downs are selected the formula shows all of the sales pitches and respective closes. However, if I select a specific Salesperson it will only show data from them (I have this part working so far). What I don't have working is to be able to show how many pitches and closes there are of a specific "Type of Sale" (ie. Auto Insurance), and then to further drill down as to the "SalesPerson", how many "Autos" from the "Yellow Pages".
What I had in mind was the ability to use any or none of the drop downs simultaneously. So I can view all pitches and closes from a single source, of a specific type, a specific salesperson, or a combination of all 3.
eg. Bob has 10 pitches and 5 closes. Bob has 4 pitches and 2 closes if auto is selected. Bob has 1 pitch and 0 closes if auto, and yellow pages is selected. If yellow pages is selected alone (without a salesperson or type) it shows 22 pitches and 8 closes.
Essentially it is a formula that filters down the data based on additional criteria not being met.
Any ideas will help.
Thanks in advance.
I have a worksheet that has multiple fields to track sales activity. I am trying to create reports that follow 3 main criteria but that material is variable. I have inserted an additional worksheet that has the 3 criteria fields as drop downs. Depending on what I select as the drop down is how I would like to create the results of the data.
So the 3 drop downs are "Salesperson", "Type of Sale", "Source". It is set up for an insurance business - just info to add clarity to my example.
The data that I am trying to compile works like this. If no drop downs are selected the formula shows all of the sales pitches and respective closes. However, if I select a specific Salesperson it will only show data from them (I have this part working so far). What I don't have working is to be able to show how many pitches and closes there are of a specific "Type of Sale" (ie. Auto Insurance), and then to further drill down as to the "SalesPerson", how many "Autos" from the "Yellow Pages".
What I had in mind was the ability to use any or none of the drop downs simultaneously. So I can view all pitches and closes from a single source, of a specific type, a specific salesperson, or a combination of all 3.
eg. Bob has 10 pitches and 5 closes. Bob has 4 pitches and 2 closes if auto is selected. Bob has 1 pitch and 0 closes if auto, and yellow pages is selected. If yellow pages is selected alone (without a salesperson or type) it shows 22 pitches and 8 closes.
Essentially it is a formula that filters down the data based on additional criteria not being met.
Any ideas will help.
Thanks in advance.