Index / Match / Sum formula to show weekly figures

There doesn't seem to be anything on your data sheet to define the year number of each week, I would guess you would want to build the named range "data" up using an if statement to select the correct row,

For example:
=IF(Returns!G1="2013",Data!$A$BG,Data!$BH:$BQ)

However, this would mean you need to put the item/return/description/price/whse/total/average columns at the start of each year. Otherwise, you would have to adjust the column that is selected for the year, for example, if you would have returned week2 in 2013, you would then add something like:
+if(Returns!G1="2014",52,0)
to move it forward 52 columns to the next year.

Would it work if I merged a row above weeks 1-52 called 2013, then 2014 etc. im a bit of a noob so don't really know how to put it in practice. thanks for the quick reply

I'm not to clued up on merged cells, I would just rename the cells "2013 wk1", "2013 wk2" and so on.

Thanks, but I cant seem to get things to work. ive uploaded a sheet again, changed text to 2013 wk1 etc for 10 weeks. ive merged the cells above and added the year to show what I was thinking. Can you enter the formulas and re upload for me. If adding 2013 / 2014 prefix is the only way, then I will add them to the finish workbook. thanks again

hmm, maybe with the merged cells with the year above them (and reverting back to WK1 not 2013WK1) you could use:
=LARGE(IF(Data!A2:BQ2="WK"&G2,COLUMN(A1:BQ1),0),IF(G1=2013,2,1))
to return the column number on the data sheet for the year/week in G1:G2

Thanks, but where am I putting this formula exactly. ive put the dates back to wk1 etc and tried the formula in any cell and it returns a value of 8 when year set to 2013 and week 1. ( a presume its an array formula as nothing else worked) when I change to week 2 I get 9. is this what was expected?

I don't really follow all the formulas in your sheet but I assumed you would use it as part of B3 to get the "column" for the week.

So instead of:
="Data!"&ADDRESS(2,MATCH("WK"&G2,Data!2:2,0))&":"&ADDRESS(300,MATCH("WK"&G2,Data!2:2,0))
you could use
="Data!"&ADDRESS(2,LARGE(IF(Data!A2:BQ2="WK"&G2,COLUMN(A1:BQ1),0),IF(G1=2013,2,1)))&":"&ADDRESS(300,LARGE(IF(Data!A2:BQ2="WK"&G2,COLUMN(A1:BQ1),0),IF(G1=2013,2,1)))

Like you say, as an array formula, which would return the address of the column for the year you want, to use as part of all your indirect functions.

mmm, cant seem to get it to work. B3 just shows now as a "value" . you did the indirect formula for me in the original post I did. I don't know anything about changing it. thanks so far

Did you confirm it with ctrl+shift+enter?

yes I did. I got an error in the indirect formula as the data had changed from B2 - B3. I changed it and it just shows as "value! ill attach to show.

Ive just gone in the formula and spotted A1:BQ1 from this part of formula (WK"&G2,COLUMN(A1:BQ1), shows in the returns tab and not picking up from the data tab?

oh right I see, it will only work for weeks 1 - 10 as it relies on there being a column for every WK number in each year.

Would it help if above week number it had year? To cross ref. so for example e1 was 2013&e2 was week number.

Thanks for the reply, but I think there is an error some where? I put qty 2 in week 10 of

EE23379RG
EE23381RG
EE23383RG

and the sheet returns

EE23380RG 7
EE23382RG 3
EE23384RG 3

every other one

I would also like to use this each coming year until the end of the sheet if possible.( it stops at 10 at the moment)

if it was a table format would it automatically pick up the data?


thanks very much for your input so far.

Ok. on the attached file, the data tab for 2014 week 10 has 3 entries highlighted in yellow. the returning result on the returns tab is different as per comments.

Thanks for the reply, but something's not working.( or more likely me !!)
I replaced D5 with =IFERROR(VLOOKUP(returns[[#This Row],[Part number]],Data!A:BQ,$C$2,FALSE),"") , but this returns a zero value. If I enter any other week numbers, they return the wrong values. ie I changed to 2013 & week 1 and added a qty of 10 to each of these
EE23379
EE23380
EE23381

result = no data

Ah, now where where we talking good work. now I need to be able to expand the weeks up to week 52 for 2014, then do the same for 2015 etc. can this be done.

Ok, changed the formulas, but still a small issue. ive added upto week 52 , 2014 and added 3 sets of data to the top 3 items. The value returned should be

EE23379 x 2
EE23380 x 2
EE23381 x 2

it returns :

EE23380
EE23381
EE23382

1 out. its the same in every field

is that the +1 in the formula??

Any ideas? Sixthsense

Thanks for coming back to me. work book attached explaining issue

That's it !! thanks for your input in solving my problem. I extended all the $56 rows to 500 for the real sheet.