Multiple formulas

[trumpetman]

I am not sure if I need a number of different formulas or if there is a multiple formula that can do what I am looking to do.

I have a spread sheet which contains a number of worksheets. Each one representing a sales person. In that worksheet the table contains which company they have sold too and what the prices were achieved to that company and category.

I want the last worksheet to be able to use a drop down list to pick the person that I am look for (vlookup or index match?). Then pick which month (vlookup or index match?) and then which category (vlookup or index match?). I am then wanting the formulas or formula to return the right persons date from the respective worksheet.

I am thinking that there is a number of index/match element plus a count element and a sum element but I am not sure if I am correct in my thinking and if so how it all pulls together.

I have attached a copy to try and show you want I am trying to achieve


sales spreadsheet.xlsx


For the rules I have tried to explain this on another site (http://www.mrexcel.com/forum/excel-q...-combined.html) but I have not been able to put attach on and I am not sure if I explained it right. Someone told me that if you post same topic on two sites the rules say you have to say.

[Richard Buttrey]

Hi,

Unfortunately you are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.


A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.


You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before jumping through hoops to get to your destination can I suggest that a better layout would be a single sheet which contains columns for

Date
Sales Person
Company
List Price
Sold price
Difference
Category

You could usefully use drop down data validation cells for Sales Person & Category.

[trumpetman]

Thanks for that. I understand that but the forms is something I was given and the boss as given me this task. I get the fact that I should go back to the boss and say that's redesign the form. Not sure of reaction but I wonder if there is a way to do what I am looking to do, granted a 2D form would have been better.

Thanks again.

[newdoverman]

Following up on Richard Buttrey's suggestion, which I totally agree with, I added two worksheets to demonstrate one way of doing this. Sheet1 has all the data and sheet2 has a Pivot Table that summarizes the data.

By using the filters on the Pivot table you can extract all the reports that you mention.

[trumpetman]

Thanks Newdoverman and Richard.

I totally get the 2D and the pivotal table can work. I know when I try and get her to change the design she will come back with so what I wanted you to do, you can't do it. I know what you are thinking about her...me too.

I will try but I just wondered if there was a formula to work with what she was looking for.

Thanks once again for your help.

[Richard Buttrey]

No. Tell her that of course you can do it - (provide the end result that is).

Ask her does she want an efficient system that will save the company time and money and provide lots of simple business information at the drag and drop of a mouse, or does she want to stay rooted in the backwoods of a forest when everyone else is back enjoying a pint in the pub.

Seriously though, ask her. If it helps point her in this direction and we'll be happy to explain (in a nice way) why she needs to sanction the change of layout.

I get seriously annoyed at management who think they know best when they know little about how to use Excel in efficient ways.

[oeldere]

Please Login or Register  to view this content.

Flowers for this statement.

[oeldere]

Please Login or Register  to view this content.

Flowers for this statement.

[newdoverman]

I can sure relate to that situation! I was the only person in the office that owned a computer and knew the difference between data sets and reports The boss wanted "pretty" first and usability didn't matter...until he needed something other than his "pretty" presentation.

[trumpetman]

newdovermap love it. Yes they always seem to think they know best. I am just thinking on my best attack with her and thinking is, is there a way with what she looking for. I was thinking that the formula would be a sum if then a number index match with a count in there. If I show her both the one that she's wants but then put the argument to her that the table way would be more appropriate for MI?

Thanks I am glad that I am not the only one with a boss like this and others have had them too. :-).

[newdoverman]

I have been retired a number of years but the memory is just like yesterday

You have to know your boss and how she reacts to various types of presentations.

I don't know how many people are involved that would have a worksheet but I have a suspicion that it is more than what you show in the example.

One problem with these worksheets as they are is that they are not proper data sets. This makes the worksheets difficult to work with to produce reports of any kind. Compare those worksheets with the single worksheet that I provided you for data entry. Being a single worksheet, there is no need to go to various worksheets to enter the data. This simple thing reduces the chance of making errors while making the data easy to work with.

If the Pivot Table approach appeals to you, get familiar with the Pivot Table so that you can click on the various filters to show individuals or groups of individuals or all individuals, with monthly stats, all stats, by category etc.

I have changed the example a little. I changed the input worksheet to a table then the Pivot Table gets its data from the table and when you add data to the table, all you have to do to the Pivot Table is right click in the table and choose refresh.

Maybe someone will supply you with formulae to do the same task. I just don't have the time....sorry.