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.

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"&COL UMN(AT1)),FALSE))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"imacken" wrote:

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.

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.

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
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"&COL UMN(AT1)),FALSE))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"imacken" wrote:

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.

OK, so how SHOULD it be done then?

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
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"&COL UMN(AT1)),FALSE))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"imacken" wrote:

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.



.

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)

--
Biff
Microsoft Excel MVP


"imacken" wrote in message
...
OK, so how SHOULD it be done then?

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
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"&COL UMN(AT1)),FALSE))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"imacken" wrote:

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.



.

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"imacken" wrote in message
...
OK, so how SHOULD it be done then?

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
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"&COL UMN(AT1)),FALSE))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"imacken" wrote:

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.



.



.

Here's a small sample file that demonstrates this.

imacken.xls 18 kb

http://cjoint.com/?mpr7mC6Aqb

--
Biff
Microsoft Excel MVP


"imacken" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"imacken" wrote in message
...
OK, so how SHOULD it be done then?

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
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"&COL UMN(AT1)),FALSE))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"imacken" wrote:

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.



.



.

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

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

imacken.xls 18 kb

http://cjoint.com/?mpr7mC6Aqb

--
Biff
Microsoft Excel MVP


"imacken" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"imacken" wrote in message
...
OK, so how SHOULD it be done then?

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
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"&COL UMN(AT1)),FALSE))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"imacken" wrote:

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.



.



.



.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"imacken" wrote in message
...
I cannot thank you enough. That has solved my problem.
Well done!

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

imacken.xls 18 kb

http://cjoint.com/?mpr7mC6Aqb

--
Biff
Microsoft Excel MVP


"imacken" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"imacken" wrote in message
...
OK, so how SHOULD it be done then?

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"Luke M" wrote in message
...
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"&COL UMN(AT1)),FALSE))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"imacken" wrote:

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.



.



.



.