Absolute reference across different sheets

[Gilles1991]

Hi,

I’m currently working on an accounting database, and I can’t seem to make absolute references across the different sheets.

This is what I tried to do (simplified):

Sheet 1:
January has a expenditures sheet, all of the expenditures in one category are added up, so cell A6 =SUM(A1:A5)
All of the expenditures in another category are added up as well, so cel B6 =SUM(B1:B5)

Sheet 2:
Next, I want to put this result in a summary, so in this sheet cell A1=‘Sheet 1’.A6

Here’s the problem: Where do I put the ‘$’-sign so I can just drag the formula down, resulting in a column that looks like this:

• cell A1=‘Sheet 1’.A6
• cell A2=‘Sheet 1’.B6
• cell A3=‘Sheet 1’.C6

etc.

Or is there another way to do this? I tried putting the dollar-sign everywhere, but nothing seemed to work, I’m at a loss… Can somebody please help me out?

[w_whalley]

In sheet 2, cell A1 enter formula ='Sheet 1'.A6

Copy this cell to the right so sheet 2 cell B1 ='Sheet 1'.B6, etc. to the end of your data.

With the cells in sheet 2 selected, press Shift-F4 to convert all the formulas to absolute references, e.g. =$'Sheet 1'.$A$6, etc.

With the cells in sheet 2 still selected, Ctrl-X to cut.

In cell A1 on sheet 2, right-click and Paste Special; check Transpose, then OK.

[Gilles1991]

Thanks a lot whalley, it works and this is gonna save me a lot of time!