Linking to cells in other work sheets

[A]

I have a spreadsheet set up with 31 work sheets, one for each day of the
month and a summary worksheet in a tabular format that calls up cells from
each day of the month .

I.e in the summary worksheet,
Cell C3 calls up =+'1st'!$B$36
Cell D3 calls up =+'2nd'!$B$36
Cell E3 calls up =+'3rd'!$B$36

How can I copy the formulas in the C Column to each of the other columns of
the table (which each relate to a specific date) without having to change
every workbook reference? I have overe 60 formulas in each column, so
changing every formula for all 31 cloumns would take forever.

Thanks in advance

 

[Pete_UK]

Assuming you have row 3 completed, all cells referencing $B$36 in
different worksheets, you can copy these down for as many as you need
(the formulae will not be changed as you have absolute references).

Then you can highlight row 4 and Edit | Replace (or CTRL-H) and you
want to "Find" $B and "Replace" it with $C and click Replace All. Then
highlight the next row, CTRL-H and you still want to find $B so you
only need to change the "Replace With" to $D and click Replace All.
Keep doing this until you have replaced all your $B references with the
correct column references - bit tedious, but you only need to do it
once if you set this up as a blank worksheet for use each month.

Hope this helps.

Pete

 

[Guest]

You may write the worksheet name in row 1, for example, and use INDIRECT to
reference the cell. In case of cell C3 it would be:
=INDIRECT(C$1 & "!$B$36")
This formula can be copied across columns, provided that you have all the
worksheet names in the first (or chosen) row.
In case you have some worksheet names with spaces, you may need to use this
variation to incorporate single quotes on the name:
=INDIRECT("'" & C$1 & "'!$B$36")

Hope this helps,
Miguel.