Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Anchoring multiple cells at once

This is a discussion on Anchoring multiple cells at once within the Excel Questions forums, part of the Question Forums category; Would anyone know how to "anchor" several cells simultaneously without going into each cell and pressing F4? Many thanks....

  1. #1
    Board Regular
    Join Date
    Apr 2010
    Posts
    128

    Default Anchoring multiple cells at once

    Would anyone know how to "anchor" several cells simultaneously without going into each cell and pressing F4?

    Many thanks.

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,654

    Default Re: Anchoring multiple cells at once

    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)?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    128

    Default Re: Anchoring multiple cells at once

    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:

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,654

    Default Re: Anchoring multiple cells at once

    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".
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Apr 2010
    Posts
    128

    Default Re: Anchoring multiple cells at once

    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

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,654

    Default Re: Anchoring multiple cells at once

    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$
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    MrExcel MVP
    Moderator

    In The Naughty Corner
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    10,079

    Default Re: Anchoring multiple cells at once

    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.

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,654

    Default Re: Anchoring multiple cells at once

    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!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    Board Regular
    Join Date
    Apr 2010
    Posts
    128

    Default Re: Anchoring multiple cells at once

    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!

  10. #10
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,654

    Default Re: Anchoring multiple cells at once

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com