Index / Match / sum formula to show weekly figures

[interested]

Hi all,
attached is a returns sheets that a qty is logged against part number every week.
Some of the data I can use pivots on ( no problem ), but I have an issue on the returns tab where I think index/match / sum or something else will be better.
At the moment because of the layout of data I have to drag each week number into the pivot value, do a greater than zero filter then summarize as a sum (yuk) I just cant get my head around index/match /sum, but can someone help?
It would be great just to be able to enter a week number, and all the warehouse, part numbers and qty came through.
There is a short explanation on the returns tab of what im looking for. Thanks in advance.
Oh its a macro book because I was trying to do the dragging week number thing in a macro but cant do it, so reason for upload.

[ragulduy]

I'd probably do something like:
=SUM(INDIRECT("Data!"&ADDRESS(2,MATCH("WK"&B1,Data!1:1,0))&":"&ADDRESS(20000,MATCH("WK"&B1,Data!1:1,0))))
on your returns sheet to return the total of the week number entered in 37. I'm sure there are many other methods though.

[interested]

Thanks for your input. This just gives me the totals for the week number. that's one element of it, so thanks. I'd like to be able to "pull" the info for week number entered. i.e. warehouse, part, qty just by changing the week number. I can pivot it but there's too much faff in sorting it out and was hoping for a easy way. With lots of help !!

[ragulduy]

Sorry, I misunderstood what you wanted. I think you can use:
in A6:
=IF(ROWS($A$1:A1)>COUNTIF(Data!$AO$2:$AO$300,">0"),"",INDEX(Data!$C$2:$C$300,SMALL(IF(Data!$AO$2:$AO$300<>"",ROW(Data!$AO$2:$AO$300)-ROW($A$2)+1),ROWS($A$1:A1))))

in B6:
=IF(ROWS($A$1:A1)>COUNTIF(Data!$AO$2:$AO$300,">0"),"",INDEX(Data!$A$2:$A$300,SMALL(IF(Data!$AO$2:$AO$300<>"",ROW(Data!$AO$2:$AO$300)-ROW($A$2)+1),ROWS($A$1:A1))))

in C6:
=IF(ROWS($A$1:A1)>COUNTIF(Data!$AO$2:$AO$300,">0"),"",INDEX(Data!$AO$2:$AO$300,SMALL(IF(Data!$AO$2:$AO$300<>"",ROW(Data!$AO$2:$AO$300)-ROW($A$2)+1),ROWS($A$1:A1))))

and then drag down.

I think this would give you week 37, to get the week number you enter I think you would need to replace AO with the column that you could pick up from a match function similar to the last post. I'd suggest doing this in a separate cell otherwise the formula would be enourmous.

[interested]

Could you post a sample back for me as im struggling to replicate your formulas, as do I remove the pivot data and place the formula in A6? I tried it above the pivot table and it did bring the info through for one warehouse, then tool number then qty, so definatly on the right track. Its the pivot table im trying to replace with this. Thanks for the work so far.

[ragulduy]

example.xlsm

See sheet1 in attachment,

I forgot to mention they were array formulas so need to be confirmed with ctrl+shift+enter, sorry!

[interested]

Its no problem, thanks again. Its so nearly there !! can it be grouped by warehouse ie CHON, CPWP etc either in separate columns or like it is? Im going to transfer it to the real document to see how it looks, as I want to see how the week numbers affect the list. Good work.

[ragulduy]

example2.xlsmI had a go but the formula is becoming to big. It's just crashing excel on my computer.
Maybe if you generate the list and then:
In E6: =IF(ISERROR(INDEX($A$6:$A$20,MATCH(0,COUNTIF($E$5:E5,$A$6:$A$20),0))),"",INDEX($A$6:$A$20,MATCH(0,COUNTIF($E$5:E5,$A$6:$A$20),0))) (As array formula)
in F6: =IF(SUMIF(A$6:A$200,"="&E7,C$6:C$200)=0,"",SUMIF(A$6:A$200,"="&E7,C$6:C$200))

then drag down and that will give you the separate headings and totals?

I've attached example again.

[interested]

Thanks. Ive actually just pivot data your data to give me the headings which is spot on. I needed a simple way to get the week data together. Ill have another look tomorrow at your sample, and report back. Many many thanks for your hard work and time put in.

[interested]

Many thanks Yudlugar, you have solved my issue very nicely. ive added a pivot table to your data which I will hide, so all I have to do now is select the week number, refresh the pivot and job done. Previously took a lot of "faff" dragging the week number into the pivot total, and manually changing the data. I would never have been able to do it without your help, so top marks for your time and efforts. ive uploaded the finished item to help others as this is a great way to get weekly info out. just change the headings to suit.