How to Copy Absolute Cell References to Other Worksheet Pages

**E*C*H*O** · 05-05-2011, 04:43 PM

I created a payroll workbook and am having trouble with absolute cells and referencing the cell that I need.

I want to be able to reference a cell on one worksheet page, for example the formula in cell E3, and have the information appear on a different worksheet within the same workbook. I've been able to do this successfully, but my next step is causing me trouble.

I want to be able to insert rows in the first worksheet (the current week's payroll), so the formula in E3 will move to E4, but the current E3 will now have the formula that I want to reference. I want the second worksheet to ALWAYS pull the information from E3.

After copying the cell reference into the second worksheet, I have tried entering it as $E$3, but when i go back to the first sheet and insert a line, then return to the second sheet, the cell reference has been updated to $E$4.

Is this possible?

Thanks for any help...

**Richard Buttrey** · 05-05-2011, 04:49 PM

Hi,

Does

Please Login or Register  to view this content.

help?

Regards

**E*C*H*O** · 05-05-2011, 05:46 PM

It worked!

I thought for sure you must have meant $E$3, but using the $E$2 worked, but I had to add a single quote before and after the Sheet1.

Can you tell me what this formula "says"? What do the 1 and 0 do? I'm not familiar what offset means, only that it worked...

=OFFSET(Sheet1!$E$2,1,0)

**Richard Buttrey** · 05-05-2011, 07:06 PM

Hi,

If you have spaces in sheet names then you do indeed need single quotes around the name. The fact that you needed them suggests that your sheet was named "Sheet 1" and not "Sheet1". It is however a good idea to include them whether or not there's a space.

This is an Offset formula. Offset formulae point to a range (or single cell) which is offset from a reference cell. In this case the reference cell is E2. The two other parameters, the values 1 & 0 refer to how many rows and columns are to be offset from the reference cell. In this case the row offset refers to row 3 (based on the reference cell row 2), and the column offset is zero, hence no offset from column E.

A similar Offset formula to E3 could also be =OFFSET($A$1,2,4).

The OFFSET() function can also take a 4th and 5th parameter, i,e,

=OFFSET(Ref cell, row, column, height, width). I won't bore you with these since you generally only need them when you're summing an offset range, but you can read up on them with the standard Excel help.

Regards

**E*C*H*O** · 05-05-2011, 09:39 PM

You are correct in that my sheet names have a space, I have given them individual names (ie. Pay Calculations).

When I googled offset I found some sample instructions at the Microsoft website. I'm going to check it out in the morning.

Thank you again for you help!