COPY DOWN INCREASING WORKSHEET REFERENCE BY 1

  • Thread starter Thread starter robzrob
  • Start date Start date
R

robzrob

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
 
Try this:

Enter this formula in C6 and copy down as needed:

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

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

then copy it down as required.

Hope this helps.

Pete



- Show quoted text -
Click to expand...

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
 
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





- Show quoted text -
Click to expand...

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



- Show quoted text -
Click to expand...

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?
 
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


Yes, I know the feeling ...

Pete
 
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
 
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





- Show quoted text -
Click to expand...

Thanks, Gord
 
Back
Top