Anchoring multiple cells at once

Bengo

Board Regular
Joined
Apr 14, 2010
Messages
210
Would anyone know how to "anchor" several cells simultaneously without going into each cell and pressing F4?

Many thanks.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Welcome to the Board!

Can you explain exactly what you mean?
Are you trying to change cell references to absolute references?
How many cells are we talking about?
What is in them now (if formulas, can you list a few)?
 
Upvote 0
Thank you!

Yes I am trying to create absolute references in a number of cells without going into each one and using F4. There are about 10 rows and 10 columns. An example of the formulas would be:
 
Upvote 0
Looks like you might have accidentally posted your reply before you entered your formula. Can you try again?

Also, unless you have some special keyboard mapping, I assume you mean F2, which is "Edit". On my computer, F4 is "Delete".
 
Upvote 0
I'll try for the 3rd time!! sorry for delay.

the formula would be something like: =D2*Sheet1!$C2$

There are multiple formulae like this within a big table and I need to copy and paste the whole table to elsewhere on the sheet with losing the references to the reference cells.

With regards to the F2/F4, my pc stepps into a cell when pressing F2, and if I select a cell within a formula and press F4, it adds the "$" signs to anchor.

Many thanks
 
Upvote 0
If you had formulas like:

=D2*Sheet1!$C$2
=D3*Sheet1!$C$2
=D4*Sheet1!$C$2
...

and you wanted to update them like this:
=$D$2*Sheet1!$C$2
=$D$3*Sheet1!$C$2
=$D$4*Sheet1!$C$2
...

You could simple do a Find and Replace and replace all instances of:
=D
with
=$D$
 
Upvote 0
Hi Bengo

ASAP Utilities allows you to copy formula exactly as they are without anchoring them down. See here.

Regarding the shortcuts, yes F4 works like a toggle to anchor row/column references when in cell edit mode.

Hope this helps.
 
Upvote 0
Regarding the shortcuts, yes F4 works like a toggle to anchor row/column references when in cell edit mode.
That's interesting. I was trying to use F4 "straight up" instead of trying it while already in edit mode (F2). I was unaware of that functionality.
Learn something new everyday!
 
Upvote 0
Joe> glad I could give something back!!

With regards to find and replace, I did try that but as the cells I want to anchor differ from formula to formula this isn't possible (would have to execute as many find and replaces as there are cells pretty much).

Jon> Thanks that looks like the answer and will do exactly what I need, now to persuade work to pay for it!! Unless there's any other way..?

Thanks all for your help!
 
Upvote 0
There is another way - involving VBA. If you have the same "format" for your formulas, you can write some script to insert the anchors into your formulas using LEFT, RIGHT, and MID functions.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top