Data analysis - want to avoid pivot tables

[Guest]

FYI - I know Pivot Tables should work for this, but I've tried and they just
don't work well with everything else in the worksheet so I'm hoping for
another alternative.

I have a list of our accounts and products they sell. I've put the accounts
into a drop down box so one may be selected to populate a chart, but now I
need to add another drop down box to select one item from all of the products
the account sells. But I'm stuck on this part - each item has five facts:
dollar sales, pound sales, unit sales, cost, price so the product code shows
up five times and I only need it to show once in the drop-down. I also want
to limit the products to just what the account carries, not all products we
sell (which happens in a pivot table). Weekly data starts in column D.

Account Product Code Fact Week 1 Week 2
Account A Product 1 Code Dollar Sales
Account A Product 1 Code Pound Sales
Account A Product 1 Code Unit Sales
Account A Product 1 Code Cost
Account A Product 1 Code Retail Price
Account A Product 2 Code Dollar Sales
Account A Product 2 Code Pound Sales
Account A Product 2 Code Unit Sales
Account A Product 2 Code Cost
Account A Product 2 Code Retail Price
Account B Product 13 Code Dollar Sales
Account B Product 13 Code Pound Sales
Account B Product 13 Code Unit Sales
Account B Product 13 Code Cost
Account B Product 13 Code Retail Price
Account B Product 8 Code Dollar Sales
Account B Product 8 Code Pound Sales
Account B Product 8 Code Unit Sales
Account B Product 8 Code Cost
Account B Product 8 Code Retail Price
Account B Product 11 Code Dollar Sales
Account B Product 11 Code Pound Sales
Account B Product 11 Code Unit Sales
Account B Product 11 Code Cost
Account B Product 11 Code Retail Price



Any suggestions are greatly appreciated.

 

[Gromit]

Hi,

One solution is easy to do, harder to explain. Here goes...

I'd use 3 drop downs, one for Account, one for Product Code and one for
Fact.
Name the ranges that contain the codes for the Product Codes, say
"AccountACodes" and "AccountBCodes". Then the linked cell for the
Product Codes dropdown has an if statement that says, IF (linked cell
from the Account drop down) = 1, "AccountACodes","AccountBCodes"). Add
a drop down for the Fact options.

Then concatenate the choices to get a three digit code. e.g. 213 would
mean Account B, Product 13 Code, Unit Sales. List out the possible
three digit combinations in your existing sheet and use a VLOOKUP or an
MATCH/OFFSET combination to get the row of data for the chart.

Hope this is even vaguely comprehensible and/or useful.

Gromit


Account
1 Account A
2 Account B

Codes available to Account A
1 Product 1 Code
2 Product 2 Code

Codes available to Account B
1 Product 13 Code
2 Product 8 Code
3 Product 11 Code

Fact
1 Dollar Sales
2 Pound Sales
3 Unit Sales
4 Cost