COPY DOWN INCREASING WORKSHEET REFERENCE BY 1
 

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?

COPY DOWN INCREASING WORKSHEET REFERENCE BY 1
 

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?

COPY DOWN INCREASING WORKSHEET REFERENCE BY 1
 

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?

COPY DOWN INCREASING WORKSHEET REFERENCE BY 1
 

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!

COPY DOWN INCREASING WORKSHEET REFERENCE BY 1
 

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 -

COPY DOWN INCREASING WORKSHEET REFERENCE BY 1
 

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.

COPY DOWN INCREASING WORKSHEET REFERENCE BY 1
 

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

COPY DOWN INCREASING WORKSHEET REFERENCE BY 1
 

On May 20, 10:50*pm, Pete_UK wrote:
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- Hide quoted text -

- Show quoted text -

Just one more thing. Would I be right in saying that the single
quotes are only necessary if you're referring to another workbook,
and, in this particular case, not necessary?

COPY DOWN INCREASING WORKSHEET REFERENCE BY 1
 

Take the bull by the horns!

I worked in a chemistry lab years ago and we did everything by hand
(multiple times). We even used slide rules! I told the lab director that
this was not very productive and that we should move into the current
century! He didn't want to hear it.

So I did it anyway! By the time I moved on to bigger and better things the
lab was fully automated!

--
Biff
Microsoft Excel MVP


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

COPY DOWN INCREASING WORKSHEET REFERENCE BY 1
 

Single quotes are required if a worksheet name has any spaces.

Thet are necessary if that is the case, even within the same workbook.


Gord Dibben MS Excel MVP

On Tue, 20 May 2008 14:57:45 -0700 (PDT), robzrob wrote:

Just one more thing. Would I be right in saying that the single
quotes are only necessary if you're referring to another workbook,
and, in this particular case, not necessary?

COPY DOWN INCREASING WORKSHEET REFERENCE BY 1
 

On May 20, 11:26*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Single quotes are required if a worksheet name has any spaces.

Thet are necessary if that is the case, even within the same workbook.

Gord Dibben *MS Excel MVP



On Tue, 20 May 2008 14:57:45 -0700 (PDT), robzrob wrote:
Just one more thing. *Would I be right in saying that the single
quotes are only necessary if you're referring to another workbook,
and, in this particular case, not necessary?- Hide quoted text -

- Show quoted text -

Thanks, Gord