 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / May 2008COPY 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? Put this in C6: =SUM(INDIRECT("'BDM"&TEXT(ROW(A1),"000")&"'!M:M")) then copy it down as required. Hope this helps. Pete > 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: > [quoted text clipped - 11 lines] > > - 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 > > Put this in C6: > [quoted text clipped - 16 lines] > > - Show quoted text - > Glad to hear that it worked for you - thanks for feeding back (always > appreciated !) [quoted text clipped - 43 lines] > > - 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 > 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 ... > [quoted text clipped - 7 lines] > > - 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? 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 >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? > Single quotes are required if a worksheet name has any spaces. > [quoted text clipped - 7 lines] > > - Show quoted text - Thanks, Gord 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!  SignatureBiff Microsoft Excel MVP Yes, I know the feeling ... Pete > 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")) SignatureBiff Microsoft Excel MVP > 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? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.