Referencing subsequent sheets by copying to subsequent columns

**dglsart** · 03-21-2011, 01:12 PM

I’ve entered a formula into an excel column that I want replicated to subsequent columns within that same sheet, with each column referencing a subsequent sheet in my workbook. For example, Column E references to Sheet 2 with this formula: E5='2-3'!$M$2, E6=’2-3’!$M$3, E7=’2-3’!$M$4, etc. Column F needs to reference to Sheet 3 with the same formula: F5=‘2-4’!$M$2, F6=’2-4’$M$3, F7=‘2-4’!$M$4 and so on. The only way I have found to do this is by dragging the column and changing each cell manually to reference the next sheet in my workbook. Any suggestions on making this easier would be greatly appreciated.

**Cutter** · 03-21-2011, 03:00 PM

You could use the INDIRECT() function but it is volatile and could cause a slowdown in your calculation times if you use a lot of them.

In E5 put this formula:

=INDIRECT("'2-"&COLUMNS($C:E)&"'!$M"&ROWS($4:5))

and copy or drag down and to right

**dglsart** · 03-21-2011, 04:00 PM

Cutter, the formula you suggested works, because the TOTAL column reflects the correct sum and when I make changes in the subsequent pages it updates the total correctly, but I can’t see the numbers in my cells where I entered your formula – it just displays ###’s. I thought maybe it was a formatting issue, but can’t find one that works. Any suggestions?