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?

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
---------------
Jacob Skaria


"ant1983" wrote:

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?

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

On Nov 26, 9:27*am, ant1983 wrote:
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?

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!

--------------------
(Ms-Exl-Learner)
--------------------



"ant1983" wrote:

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?

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.
--
David Biddulph

"ant1983" wrote in message
...
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?

Copy the formula from the formula bar.

Select cell A3
Highlight the formula in the formula bar
Right clickCopy
Hit Esc (escape)
Select the cell where you want the formula copied to
Right clickPaste

--
Biff
Microsoft Excel MVP


"ant1983" wrote in message
...
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?

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*



"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"ant1983" wrote:

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?

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

On Nov 26, 7:04*pm, ant1983 wrote:
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*



"Jacob Skaria" wrote:
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
---------------
Jacob Skaria

"ant1983" wrote:

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?- Hide quoted text -

- Show quoted text -

Sorry, I referred to C9, but your formula is in G9.

Pete

On Nov 26, 11:44*pm, Pete_UK wrote:
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

On Nov 26, 7:04*pm, ant1983 wrote:



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*

"Jacob Skaria" wrote:
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
---------------
Jacob Skaria

"ant1983" wrote:

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?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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
---------------
Jacob Skaria


"Pete_UK" wrote:

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

On Nov 26, 7:04 pm, ant1983 wrote:
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*



"Jacob Skaria" wrote:
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
---------------
Jacob Skaria

"ant1983" wrote:

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?- Hide quoted text -

- Show quoted text -

.