Prob an easy one: How do i copy a formula EXACTLY as it is


A

ant1983

Scenario:

A1 + A2 = A3

so in A3 i have "=A1+A2"

So if i want to copy that formula in lets say B3 then it would adapt the
reference so it would ready "=B1+B2"

I dont want that - how can i keep that original refernces wherever i copy
the formula?
 
Ad

Advertisements

J

Jacob Skaria

Use absolute referencing

=$A$1+$A$2

Below are the different reference styles.

A1 Relative referencing. Both column and row will change if you copy or drag
the formula.
$A1 The column reference is fixed and will not change
A$1 The row reference is fixed and will not change.
$A$1 Column and row reference are fixed.

If this post helps click Yes
 
P

Pete_UK

You could re-write the formula as:

=$A$1 + $A$2

Then when you copy it to another cell it will not change the cell
references.

Hope this helps.

Pete
 
M

Ms-Exl-Learner

Method 1:-
Place the cursor in A3 cell and give F2 and hold shift and press Home Key
and press Cntrl+C. Now place the cursor in B3 and give cntrl+v.

Method 2:-
In another method if you want to make cell reference as stable then in A3
cell give F2 which will show like this
=A1+B1
take the cursor in between Column Name & Cell No =A1 and press F4 now your
formula will show like this =$A$1
In the same manner place the cursor in between B1 and press F4 again it will
show like this +$B$1. Now your total formula will look like this =$A$1+$B$1

If your formula is protected with the $ Symbol then you can just copy that
cell and paste it in any of the cell which you have mentioned in your above
post. No need to follow the Method 1 when your formula is protected with the
$ symbol in between the cell reference.

If this post helps, Click Yes!
 
D

David Biddulph

You need =$A1+$A2, or =$A$1+$A$2 if you also want to be able to copy down
without it changing.

Look in Excel help for the difference between absolute and relative
addressing.
 
T

T. Valko

Copy the formula from the formula bar.

Select cell A3
Highlight the formula in the formula bar
Right click>Copy
Hit Esc (escape)
Select the cell where you want the formula copied to
Right click>Paste
 
Ad

Advertisements

A

ant1983

Oh damn well now i wished i used my actual formula :) Used that one for
simplicity but have no idea how to apply that to mine so can you help me out?
:)

So it looks like this:

In cell G9 it is: =COUNTIFS('FNB HR'!O:O,A9,'FNB HR'!B:B,"FNB")
In cell N9 it should be: =COUNTIFS('FNB HR'!O:O,A9,'FNB HR'!B:B,"FNB",'FNB
HR'!Q:Q,"Yes")

etc...

So basically what i do now is i double click G9, then copy the actual
formula text, then double click N9 and paste it and hit enter and then i just
kinda add that last bit manually :)

*gulp*
 
P

Pete_UK

The formula in C9 could be changed to this:

=COUNTIFS('FNB HR'!$O:$O,$A$9,'FNB HR'!$B:$B,"FNB")

and then you can copy this to N9 and it will not change, so you can
then add the final part to it.

Note that if you want to copy the formula down, so that on row 10 it
will refer to cell A10, then you should omit the $ in front of the 9,
i.e. $A9.

Hope this helps.

Pete
 
Ad

Advertisements

J

Jacob Skaria

In edit mode place the cursor near/between the cell reference and press F4
once to change the reference to abosolute.

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top