Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

ms excel training london - absolute cell references

Forum home » Delegate support and help forum » Microsoft Excel Training and help » ms excel training london - Absolute cell references

ms excel training london - Absolute cell references

ResolvedVersion Standard

Absolute cell references

Hi, I have a column full of figures that are the result of a sum.

I can link these cells to a new worksheet etc but then if I want those figures to remain constant, how do I change the cell to reflect this?

Thanks,

Kate

Edited on Mon 20 Nov 2006, 12:23

RE: Absolute cell references

Hi Kate,

There's two ways to interpret your question.

1) You can reference a cell on another sheet, which means if you update the value on Sheet 1, it is also updated automatically on Sheet 2.

To do this, use the dollar signs to ensure absolute referencing. eg. =Sheet1!$C$5
This will always reference Sheet 1's C5 cell, and if you move Sheet 1's C5 cell to somewhere else, Sheet 2 will again automatically update with the new location of the cell.

Hint: You can press F4 to cycle through the cell referencing options when in Edit Cell mode.

2) Another option you have is to paste the values from Sheet 1 into Sheet 2, meaning if you later update a Sheet 1 value, Sheet 2 will NOT be updated.

To do this, copy the cells from Sheet 1 as normal (select with mouse then Control+C or right click -> copy). Switch to Sheet 2, then RIGHT click where you want to paste and select Paste special... from the pop-up menu. Here you'll find a range of items to paste, the third one down being 'Values'.

I hope this answers your question.

Regards, Rich

Excel tip:

Use RANDBETWEEN to generate random numbers

There may be occassions where you need to generate random numbers in your spreadsheet. Use the RANDBETWEEN function to generate random numbers between two values that you specify.

The function looks like this:

=randbetween(LOW,HIGH)

where LOW is the lowest number you want generated; and HIGH is the highest number you want generated.

This formula will work with both positive and negative LOWs and HIGHs. Also it will only generate integer numbers unless forced to do otherwise by the following:

=randbetween(LOW*10^PRECISION,
HIGH*10^PRECISION)/(10^PRECISION).

where PRECISION represents the levels of decimal precision needed (i.e. if you need numbers with one decimal place, PRECISION would be 1; 2 for two decimal places and so on).

One final note, if the RANDBETWEEN formula does not work in your spreadsheet or returns a "#NAME" error, you need to install the Analysis Toolpak Add-In. You will need to press F2 then Enter following the installation.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.12 secs.