Intersection help

[Ray]

hi -

I need some help building a formula, but not sure exactly how to
describe except to first describe my data layout:

Sheet A:
cell b1 - user-selected week (by validation)
cell b2 - user-selected category (by validation)
cells A5:A25 - store numbers
cells B5:B25 - where the formula will go

Sheet B:
cells D5:BD5 - week
cells A6:A? - store numbers (range will vary)
cells B6:B? - categories
* there are approx 40 Categories for each store

So, the formulas in SheetA, B5:B25 should pull data from the
intersection of week & Store-Category ...

Can anyone help?

Thx, ray

 

[Guest]

try:

=SUMPRODUCT(--(SheetB!$D$5:$BD$5=B1)*(SheetB!B6:B100=B2)*(SheetB!A6:A100=A5)*(Sheet3!D6:BD100))

Change the 100 to suit.

 

[Guest]

...typo ...

Sheet3 should be SheetB

 

[Dave Peterson]

I think that this will work.

In Sheet2:
I put the table in D6:J12 (change this to what you need).
The store numbers in A6:A12
The categories in B6:B12
The week numbers in D5:J5
(I didn't want to build a table all the way to column BD for testing.)

Then in Sheet1, I had:
The Week number in B1
The category id in B2
The store numbers in A5

I put this array formula in sheet1 cell B5:

=IF(COUNTA($B$1:$B$2)<2,"",
INDEX(Sheet2!$D$6:$J$12,
MATCH(1,($A5=Sheet2!$A$6:$A$12)*($B$2=Sheet2!$B$6:$B$12),0),
MATCH($B$1,Sheet2!$D$5:$J$5,0)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in sheet2 in
xl2007.

Then I could copy to the right and select that row of formulas and copy it down
as far as required.