How do I copy and paste a linked formula?

**mrssteelerhall** · 03-13-2013, 12:20 PM

I need to copy and paste a linked formula and have that formula update automatically. Here's what i am trying to do...I have a workbook with 31 worksheets, one for each day of the month. Each worksheet has two columns; one with opening numbers and one with closing numbers (to track sales of gallons of gasoline) I need the closing numbers to link to the next worksheet under opening numbers. I have created the linked formula but when I try to copy and paste it to a new worksheet the original cell reference is still in there. Is there a way for the formula to automatically update referencing the previous worksheet? Doing this by hand is becoming tedious.

**Pauleyb** · 03-13-2013, 03:24 PM

Assuming your sheets are labeled Sheet1, Sheet2, ..., Sheet31. Assuming the closing numbers are in column B. Try putting this in A1 of your sheets.
=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+6,256)-1&"!B"&ROW())