+ Reply to Thread
Results 1 to 5 of 5

How to fix a large range of cells??

  1. #1
    Registered User
    Join Date
    06-15-2009
    Location
    Queretaro, Mexico
    MS-Off Ver
    Excel 2003
    Posts
    19

    Question How to fix a large range of cells??

    Hi everyone,

    I'm working on a database that requires me to copy and move some formulas around, but those formulas require me to fix some of the references they have, is there a way to avoid doing it manually?

    I’m attaching a sample file that explains more clearly....

    Cheers,
    Kinauz
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: How to fix a large range of cells??

    The following macro changes all relative formulae into absolute formulae in the selected range.

    Please Login or Register  to view this content.
    EDITED - Mistake made - sorry.


    You should be able to adapt this to partial absolute formulae with inclusion of R or C just before the square brackets.
    Last edited by mrice; 09-26-2009 at 08:10 AM. Reason: Mistake
    Martin

  3. #3
    Registered User
    Join Date
    06-15-2009
    Location
    Queretaro, Mexico
    MS-Off Ver
    Excel 2003
    Posts
    19

    Re: How to fix a large range of cells??

    thnx for the response, but I'm afraid that the macro changes all cell references to $L$6. Couldn't make it work any other way.....got any ideas?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: How to fix a large range of cells??

    I don't understand your question about absolute references at all, but if you want to lookup data in the second table based on the name and month in the first table, in B2 and copy down and across,

    =INDEX($M$7:$R$12, MATCH($A2, $M$7:$M$12, 0), MATCH(B$1, $M$7:$R$7, 0))
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: How to fix a large range of cells??

    Oops!

    Try

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1