Ask Your Question

Absolute reference across different sheets [closed]

asked 2014-02-19 12:28:09 +0200

Gilles1991 gravatar image

updated 2020-10-28 13:17:25 +0200

Alex Kemp gravatar image


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


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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 11:12:07.751625

1 Answer

Sort by » oldest newest most voted

answered 2014-02-19 20:46:34 +0200

w_whalley gravatar image

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.

edit flag offensive delete link more


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

Gilles1991 gravatar imageGilles1991 ( 2014-02-21 06:36:02 +0200 )edit

Question Tools

1 follower


Asked: 2014-02-19 12:28:09 +0200

Seen: 24,845 times

Last updated: Feb 19 '14