I've got =SUM(BDM001!M;M) in C6, ie referring to column M in worksheet
BDM001. In C7, I want =SUM(BDM002!M:M0, ie referring to worksheet
BDM002, in C8 I want =SUM(BDM003!M:M), etc. How do I copy down?

Put this in C6:

=SUM(INDIRECT("'BDM"&TEXT(ROW(A1),"000")&"'!M:M"))

then copy it down as required.

Hope this helps.

Pete

On May 20, 9:17*pm, robzrob wrote:
I've got =SUM(BDM001!M;M) in C6, ie referring to column M in worksheet
BDM001. *In C7, I want =SUM(BDM002!M:M0, ie referring to worksheet
BDM002, in C8 I want =SUM(BDM003!M:M), etc. *How do I copy down?

On May 20, 9:47*pm, Pete_UK wrote:
Put this in C6:

=SUM(INDIRECT("'BDM"&TEXT(ROW(A1),"000")&"'!M:M"))

then copy it down as required.

Hope this helps.

Pete

On May 20, 9:17*pm, robzrob wrote:



I've got =SUM(BDM001!M;M) in C6, ie referring to column M in worksheet
BDM001. *In C7, I want =SUM(BDM002!M:M0, ie referring to worksheet
BDM002, in C8 I want =SUM(BDM003!M:M), etc. *How do I copy down?- Hide quoted text -

- Show quoted text -

Thanks Pete - It works. Don't suppose you'd care to explain, would
you? I'm inexperienced and can't work out why it works!

Glad to hear that it worked for you - thanks for feeding back (always
appreciated !)

The INDIRECT function allows you to build up a reference as a string -
the common parts to your requirement was a sheet name that began with
"BDM" and a range reference to column M in that sheet, and the only
thing that changes as you copy the formula down is the number used in
the sheet name.

The function ROW(A1) will return 1, but as the formula is copied down
this becomes ROW(A2), ROW(A3), ROW(A4) etc which in turn returns 2, 3,
4 for successive rows. You indicated that the sheet number has two
leading zeros, so TEXT(ROW(A1),"000") actually returns 001, which is
then joined to the other two parts of the string to make the reference
you require.

Incidentally, the INDIRECT function will only work with workbooks that
are open - in your case the sheets were all contained within the same
workbook (which is always a good idea, although not always
achievable).

Hope this helps.

Pete

On May 20, 10:02*pm, robzrob wrote:
On May 20, 9:47*pm, Pete_UK wrote:





Put this in C6:

=SUM(INDIRECT("'BDM"&TEXT(ROW(A1),"000")&"'!M:M"))

then copy it down as required.

Hope this helps.

Pete

On May 20, 9:17*pm, robzrob wrote:

I've got =SUM(BDM001!M;M) in C6, ie referring to column M in worksheet
BDM001. *In C7, I want =SUM(BDM002!M:M0, ie referring to worksheet
BDM002, in C8 I want =SUM(BDM003!M:M), etc. *How do I copy down?- Hide quoted text -

- Show quoted text -

Thanks Pete - It works. *Don't suppose you'd care to explain, would
you? *I'm inexperienced and can't work out why it works!- Hide quoted text -

- Show quoted text -

On May 20, 10:21*pm, Pete_UK wrote:
Glad to hear that it worked for you - thanks for feeding back (always
appreciated !)

The INDIRECT function allows you to build up a reference as a string -
the common parts to your requirement was a sheet name that began with
"BDM" and a range reference to column M in that sheet, and the only
thing that changes as you copy the formula down is the number used in
the sheet name.

The function ROW(A1) will return 1, but as the formula is copied down
this becomes ROW(A2), ROW(A3), ROW(A4) etc which in turn returns 2, 3,
4 for successive rows. You indicated that the sheet number has two
leading zeros, so TEXT(ROW(A1),"000") actually returns 001, which is
then joined to the other two parts of the string to make the reference
you require.

Incidentally, the INDIRECT function will only work with workbooks that
are open - in your case the sheets were all contained within the same
workbook (which is always a good idea, although not always
achievable).

Hope this helps.

Pete

On May 20, 10:02*pm, robzrob wrote:



On May 20, 9:47*pm, Pete_UK wrote:

Put this in C6:

=SUM(INDIRECT("'BDM"&TEXT(ROW(A1),"000")&"'!M:M"))

then copy it down as required.

Hope this helps.

Pete

On May 20, 9:17*pm, robzrob wrote:

I've got =SUM(BDM001!M;M) in C6, ie referring to column M in worksheet
BDM001. *In C7, I want =SUM(BDM002!M:M0, ie referring to worksheet
BDM002, in C8 I want =SUM(BDM003!M:M), etc. *How do I copy down?- Hide quoted text -

- Show quoted text -

Thanks Pete - It works. *Don't suppose you'd care to explain, would
you? *I'm inexperienced and can't work out why it works!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Thanks again. Sorry for the impatience, but I'm absolutely fascinated
by Excel and only wish I had more tasks for which it's appropriate.
My workplace is rather backward. I offer: 'I could write a
spreadsheet for that' but they're not interested - and so much time
could be saved - if they only knew. It's such a waste.

Yes, I know the feeling ...

Pete

On May 20, 10:40*pm, robzrob wrote:
Thanks again. *Sorry for the impatience, but I'm absolutely fascinated
by Excel and only wish I had more tasks for which it's appropriate.
My workplace is rather backward. *I offer: 'I could write a
spreadsheet for that' but they're not interested - and so much time
could be saved - if they only knew. *It's such a waste

Try this:

Enter this formula in C6 and copy down as needed:

=SUM(INDIRECT("BDM"&TEXT(ROWS(C$6:C6),"000")&"!M:M "))

--
Biff
Microsoft Excel MVP


"robzrob" wrote in message
...
I've got =SUM(BDM001!M;M) in C6, ie referring to column M in worksheet
BDM001. In C7, I want =SUM(BDM002!M:M0, ie referring to worksheet
BDM002, in C8 I want =SUM(BDM003!M:M), etc. How do I copy down?