change absolute reference in a formula

**mingali** · 09-09-2009, 12:58 PM

Hi

I have been making a mistake by not including $ in my formula for absolute reference. Now, i could like to amend this mistake. Is there a quick way of doing it?

Basically, i have 24 columns and 400+ rows for 4 worksheets each to amend. Hence, i am looking for a short cut rather than just to press "F4" button for each reference.

Thanks for the help in advance.

**zbor** · 09-09-2009, 01:11 PM

If your formula is consistent you can rename only first formula and drag down.

By consistent I mean if in C1 is A1+B1 then it's same all way down (A2+B2 etc)

**Cheeky Charlie** · 09-09-2009, 01:21 PM

urk!

Depending on the references and the rest of the formulae you could use find & replace...

If there is a pattern to the formulae you could replicate it with index() and row() column() counters.

CC

**mingali** · 09-09-2009, 01:45 PM

Hi,

I should have attached a workbook for reference.

I hereby attached one now. in sheet 1, column b to col n is jan - dec 09 and its total. it links to sheet 2. Now in col P to AA in sheet 1, i need the formula stayed the same as col b to col n, eg col p have the same formula link to the same cells. so, i need col b to n to be absolute reference in its formula, otherwise, when i copy and paste to col p to aa, all the link will be adjusted accordingly.

It is late and i am tired, hopefully you guys know what i am trying to say.

Thanks

**teylyn** · 09-09-2009, 05:56 PM

OK, here's what you need to do:

click on B3 and hit F2 to edit the formula.
in the formula bar, highlight the formula and copy it
hit ESC to cancel out of the formula bar
click P3 and hit F2
in the formula bar delete what's in there now and then paste what you copied. Hit Enter.
P3 should now have the the same formula as B3, i.e.

=SUM(Sheet2!B3,Sheet2!B16)

Now copy P3 down to P13 and then copy column P across to column AA

does that help?

**mingali** · 09-13-2009, 05:49 AM

Hi Teylyn,

Your suggestion works fine. However, is there a method to include Absolute ref (aka $) in the fomula? Because i need to copy and paste it on a monthly basis.

Thanks