Link to home
Start Free TrialLog in
Avatar of Michael
MichaelFlag for United States of America

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())<WEEKNUM(TODAY()-91),CONCATENATE("WEEK1",":","WEEK",WEEKNUM(TODAY()),"!V55"),CONCATENATE("WEEK",WEEKNUM(TODAY()-91),":","WEEK",WEEKNUM(TODAY()),"!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(TODAY())<WEEKNUM(TODAY()-91),CONCATENATE("WEEK1",":","WEEK",WEEKNUM(TODAY()),"!V55"),CONCATENATE("WEEK",WEEKNUM(TODAY()-91),":","WEEK",WEEKNUM(TODAY()),"!V55")))
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Well the result of your formula is a string !!! I don't think tht is what you want the formula resulted to:
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
Avatar of Michael

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).
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!V55";"WEEK5!V55";"WEEK6!V55";"WEEK7!V55";"WEEK8!V55";"WEEK9!V55";"WEEK10!V55";"WEEK11!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
Avatar of Michael

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