MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 ?