Michael
asked on
Excel SUM formula needs variable sheet ranges
We have a mammoth workbook where each sheet represents (and is named for) a week of the year and rows and columns with each sheet show information for individual employees. I have been asked to create a Summary sheet where one of the things to display is Trailing 13 Weeks table. I can write a formula which gives me the text value that I am looking for:
=IF(WEEKNUM(TODAY())<WEEKN UM(TODAY() -91),CONCA TENATE("WE EK1",":"," WEEK",WEEK NUM(TODAY( )),"!V55") ,CONCATENA TE("WEEK", WEEKNUM(TO DAY()-91), ":","WEEK" ,WEEKNUM(T ODAY()),"! V55")
but if I put that as part of an INDIRECT formula in a SUM formula, it errors out. I assume that the INDIRECT formula wants an individual cell reference as that is where the error (#REF) happens. If I leave off the INDIRECT formula, I get a #VALUE error (I assume it reads it as text).
=SUM(INDIRECT(IF(WEEKNUM(T ODAY())<WE EKNUM(TODA Y()-91),CO NCATENATE( "WEEK1",": ","WEEK",W EEKNUM(TOD AY()),"!V5 5"),CONCAT ENATE("WEE K",WEEKNUM (TODAY()-9 1),":","WE EK",WEEKNU M(TODAY()) ,"!V55")))
=IF(WEEKNUM(TODAY())<WEEKN
but if I put that as part of an INDIRECT formula in a SUM formula, it errors out. I assume that the INDIRECT formula wants an individual cell reference as that is where the error (#REF) happens. If I leave off the INDIRECT formula, I get a #VALUE error (I assume it reads it as text).
=SUM(INDIRECT(IF(WEEKNUM(T
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I know the first formula results in a string; hence the need for INDIRECT. It is that string value that would normally be in the SUM formula.
I need the "3d" reference; that is the variable part.
For the YTD table on the same sheet I have a formula that reads =SUM(Week1:Week52!V55)
For the TTW table, I need essentially the same formula result except that instead of it always being weeks 1-52, it needs to reference the previous 13 weeks (or less if we are still in the first quarter).
I need the "3d" reference; that is the variable part.
For the YTD table on the same sheet I have a formula that reads =SUM(Week1:Week52!V55)
For the TTW table, I need essentially the same formula result except that instead of it always being weeks 1-52, it needs to reference the previous 13 weeks (or less if we are still in the first quarter).
My suggested formula above is summing V55 in the relevant sheets, but it does it in a different way - rather than creating a 3d reference it produces an "array" like this:
{"WEEK1!V55";"WEEK2!V55";" WEEK3!V55" ;"WEEK4!V5 5";"WEEK5! V55";"WEEK 6!V55";"WE EK7!V55";" WEEK8!V55" ;"WEEK9!V5 5";"WEEK10 !V55";"WEE K11!V55"}
You can then use that in a SUMIF fomula to sum V55 on all sheets listed
That will give you the same weeks as your current formula - can you try it?
regards, barry
{"WEEK1!V55";"WEEK2!V55";"
You can then use that in a SUMIF fomula to sum V55 on all sheets listed
That will give you the same weeks as your current formula - can you try it?
regards, barry
ASKER
Thanks; the further explanation helped clarify it for me.
I note that as the original logic stands (and I didn't change that with my suggestion) you will normally be summing 14 weeks, e.g. in week 20 your formula will give you
=WEEK7:WEEK20!V55
which is actually 14 different weeks, so if you actually want the last 13 including this week you probably need to change the -91 to -84
regards, barry
=WEEK7:WEEK20!V55
which is actually 14 different weeks, so if you actually want the last 13 including this week you probably need to change the -91 to -84
regards, barry
WEEK1:WEEK11!V55
Not even this on an indirect will lead you somewhere !!!
Can you post a small sample of the data I can then fix it for you ?
gowflow