You are here:

Excel/Dragging formulas across multiple tabs

Question
I am making a workbook to track my sales weekly over the course of the year vs my YTD sales target, so i have 52 tabs and every week my YTD target will increase by one hundred thousand. So for example the formula for Sheet 2 would be ='Sheet1'!A50+100000 then for Sheet 3 it would be ='Sheet2!A50+100000 then Sheet 4 would be ='Sheet3!A50+100000, etc.  What I'm wondering is how i can drag this formula through the 52 tabs without having to manually imput all the formulas?

Thanks!

Ky:

Assuming your sheets are actually named "Sheet01", "Sheet02", etc, then place this following formula into the cell of your choice in "Sheet02".  It is important to have the leading zero on the first 10 sheets.  It is also important the names are "Sheet".  If it is something different then the formula will need to be modified:

=INDIRECT("Sheet"&TEXT(RIGHT(MID(CELL("filename",B35),FIND("]",CELL("filename",B35))+1,255),2)-1,"00")&"!B35")+83000

Then, copy the above cell.  Select "Sheet3" through "Sheet52" - this will group all of those sheets together.  Select whatever cell you want the formula in for all sheets and then choose paste.  This will paste this formula into the same cell of "Sheet3" through "Sheet52".

Right-click on one of the selected sheet tabs and choose "ungroup sheets" before making any further changes (or just select sheet1 - it will do the same thing".

Basically the above formula finds the sheet name of the current sheet the formula is on.  It assumes the last two characters of the sheet name are the number (01, 02, 52, etc).  It then uses an indirect formula to find the value of cell A50 on whatever sheet name is one less than the current.

Please let me know if you have any follow-up questions as this is a fairly complicated formula.  I can be contacted at Lotus@swbell.net (you are welcome to email me the spreadsheet and I can assist with the addition of the formula).

If you are satisfied with this solution then I suggest making a donation to this expert by clicking the done button at the following link:

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Nathan

Expertise

This expert requires a donation of \$5 for questions answered (satisfaction guaranteed). Isn't \$5 a value for this experts time?
Microsoft Excel questions related to advanced formulas, Pivot Tables, filters, forms, graphs, and just about anything else (EXCEPT Visual Basic Coding/Programming and Macros, I don't answer questions in those categories).

Experience

I have been using spreadsheets since Lotus 1-2-3 was released.

Education/Credentials
Certified Public Accountant (CPA)