Passing text into a formula?

[imacken]

I have a payroll file that contains a sheet for every week of the year, and
in order to calcualte holiday pay, I use a formula to calculate total hours
worked by each employee, such as:
=SUM('12 Apr:22 Nov'!AT16) where '12 Apr:22 Nov' is obviously the range of
dates.
This is a pain as I have to manually enter any date changes to all employees
rows - several dozen! - each time.
How can I put the date range into a cell as text and get the formula to
relate to it?
Thanks.

 

[Luke M]

Let's say first sheet name is in A1, second sheet name is in A2.

=SUM(INDIRECT("'"&A1&":"&A2&"'!AT16"))

Note that in this style, the AT16 reference is static. If you need it to
change as you copy cell, you can try this:

=SUM(INDIRECT("'"&A1&":"&A2&"'!R"&ROW(A16)&"C"&COLUMN(AT1)),FALSE))

 

[T. Valko]

Let's say first sheet name is in A1, second sheet name is in A2.

=SUM(INDIRECT("'"&A1&":"&A2&"'!AT16"))

Can't reference a "range" of sheets like that in INDIRECT.

If you would have tested it you would have gotten a #REF! error.

 

[imacken]

OK, so how SHOULD it be done then?

Can't reference a "range" of sheets like that in INDIRECT.

If you would have tested it you would have gotten a #REF! error.

--
Biff
Microsoft Excel MVP





.

 

[T. Valko]

OK, so how SHOULD it be done then?

It's rather complicated!

=SUM('12 Apr:22 Nov'!AT16) where '12 Apr:22 Nov'
is obviously the range of dates.

12 Apr 2009 = Sunday
22 Nov 2009 = Sunday

So, I'm assuming your sheet names are for the Sunday dates for the year:

12 Apr
19 Apr
26 Apr
3 May
10 May
17 May
etc
etc

A1 = 12 Apr (as a true Excel date)
B1 = 22 Nov (as a true Excel date)

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(A1-7+ROW(INDIRECT("1:"&(B1-A1)/7+1))*7,"d
mmm")&"'!AT16"),"<1E100"))

Does the same thing as:

=SUM('12 Apr:22 Nov'!AT16)

 

[imacken]

Thanks for that. It just won't work. I'm not sure what is supposed to be
between "d and mmm") but I just get a #REF! error. (Not clear from the line
break in your post.)
Although the sheets are called by the Sunday dates, they are not labelled
with date format, just text.
For example, the range SUM('12 Apr:19 Apr'!AT16) would simply be the total
of the cell AT16 in the 2 sheets called 12 Apr and 19 Apr.
Any more help would be appreciated.

 

[T. Valko]

Here's a small sample file that demonstrates this.

imacken.xls 18 kb

http://cjoint.com/?mpr7mC6Aqb

 

[imacken]

I cannot thank you enough. That has solved my problem.
Well done!

 

[T. Valko]

You're welcome. Thanks for the feedback!