Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Intersection help
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Intersection help
try:
=SUMPRODUCT(--(SheetB!$D$5:$BD$5=B1)*(SheetB!B6:B100=B2)*(SheetB !A6:A100=A5)*(Sheet3!D6:BD100)) Change the 100 to suit. "Ray" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Intersection help
...typo ...
Sheet3 should be SheetB "Ray" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Intersection help
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. Ray wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Intersection of two lines | Charts and Charting in Excel | |||
intersection of 2 graphs | Charts and Charting in Excel | |||
Intersection | Excel Discussion (Misc queries) | |||
intersection naming | Excel Discussion (Misc queries) | |||
Intersection of a graph | Excel Worksheet Functions |