Problem with copying formula in Excel 2010

[qaschele11]

I am new to Excel so please forgive my ignorance.
I have a large workbook containing 44 worksheets, one of which is a summary page.
On the summary worksheet I want to show the results (which are comments on a unit of study) from all the other sheets. I want to link the row on the summary page back to the relevant worksheet and this is my formula: =Form1!$A$34.
When I try to drag and copy, the formula does not automatically increment; eg =Form2!$A$34.
What am I doing wrong?

Any help would be very much appreciated.

 

[Derek Brown]

Hello and welcome to The Board.
You can probably get it to work using the INDIRECT function:
=INDIRECT("Form"&ROW(A1)&"!$A$34")
The reference to cell A1 will change to A2, A3 etc. as you copy it down.
But be warned about having too many of such formulas in a workbook otherwise it may slow down calculations. INDIRECT, for example, is a 'volatile' function that will recalculate every time any cell changes, even if not directly affected. More details at:
http://www.decisionmodels.com/calcsecretsi.htm
You may also find the following of interest when creating summary sheets:
1. The Consolidate Function:
http://www.youtube.com/results?search_query=mrexcel+consolidate&aq=f
2. 3D Spearing:
http://www.youtube.com/watch?v=moMzsM-KwgQ