Copying Formulas
Posted by Carrie on December 11, 2001 7:18 AM
I am rolling up numbers from several different sheets (Sheets2-20)in an Excel workbook into a single sheet (Sheet1). The rows in Sheet1 each represent a different sheet. The columns in Sheet1 represent rows within the other Sheets2-20. Therefore, I would like to manually link each cell for the first row (i.e. Sheet2) in Sheet 1, but then copy that down so that it changes the formula as I copy down. For example, Cell A1 in Sheet1 is: =Sheet2!F13. Cell B1 in Sheet1 is: =Sheet2!F14. I would like to copy it down so that Cell A2 in Sheet1 is: =Sheet3!F13. And Cell B2 in Sheet1 is: =Sheet3!F14. And so on through Sheet 20.
Is there a way to accomplish this???
Posted by Juan Pablo G. on December 11, 2001 7:21 AM
You have to use the indirect function, something like, in A1:
=INDIRECT("Sheet" & ROW() + 1 & "!F" & COLUMN() + 12)
And drag across and down.
Juan Pablo G.
Posted by Carrie on December 11, 2001 9:09 AM
I'm not sure I explained what I need correctly. This may be clearer:
I am rolling up numbers from several different sheets (Sheets2-20)in an Excel workbook into a single sheet (Sheet1). The rows in Sheet1 each represent a different sheet (Sheet2-Sheet20). I would like to link each cell for the first row in Sheet 1 to Sheet 2, but then copy that down so that it changes the formula as I copy down. So as I copy the formula down, only the sheet would change, not the cell. For example, the formulas in Column A would look like this after I copy the formula:
Cell A1: =Sheet2!$F$13
Cell A2: =Sheet3!$F$13
Cell A3: =Sheet4!$F$13
Column B would look like this after I copy the formula:
Cell B1: =Sheet2!$F$14
Cell B2: =Sheet3!$F$14
Cell B3: =Sheet4!$F$14
Is there a way to accomplish this???
Posted by Juan Pablo G. on December 11, 2001 10:02 AM
What's wrong with my formula ?
