+ Reply to Thread
Results 1 to 20 of 20

Convert Mulitple Formulas to Absolute References

  1. #1
    Registered User
    Join Date
    08-08-2019
    Location
    Surrey, England
    MS-Off Ver
    Excel 2016
    Posts
    9

    Convert Mulitple Formulas to Absolute References

    Hi

    Does anyone have a macro that can convert the following formula to entirely absolute references? I have about 900 rows to convert and as you can see the reference in the second set of parentheses isn't completely absolute. This is because I need to reference the specific row that it is on. However, when I sort the data I need it to lock in its original row. Any ideas?

    =INDEX('Title1'!$A$1:$H$126, MATCH(Master!$A2,'Title1'!$A:$A,0), MATCH(Master!$K$1,'Title1'!$1:$1,0))

  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Convert Mulitple Formulas to Absolute References

    Good morning Lynnith

    Try running the macro here on a copy of your workbook and see if it works.
    It should work fine, as long as your formula length isn't greater than 255 characters (the one you've stated above is 101).

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Registered User
    Join Date
    07-21-2019
    Location
    Italy
    MS-Off Ver
    2016
    Posts
    58

    Re: Convert Mulitple Formulas to Absolute References

    Hi,
    use Modification->Replace $A2 to $A$2

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,257

    Re: Convert Mulitple Formulas to Absolute References

    Modification???

    Do you mean Find & Replace?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    07-21-2019
    Location
    Italy
    MS-Off Ver
    2016
    Posts
    58

    Re: Convert Mulitple Formulas to Absolute References

    Yes, sorry I was wrong to write
    Last edited by AliGW; 08-08-2019 at 04:35 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Registered User
    Join Date
    08-08-2019
    Location
    Surrey, England
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Convert Mulitple Formulas to Absolute References

    Thank you MrGes, unfortunately the reference is different on each row so am unable to use the find and replace function, but thank you.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,257

    Re: Convert Mulitple Formulas to Absolute References

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,603

    Re: Convert Mulitple Formulas to Absolute References

    Please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version. Your profile says "Windows Vista" which is your OS, not your Office or Excel version.
    Thanks

  9. #9
    Registered User
    Join Date
    08-08-2019
    Location
    Surrey, England
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Convert Mulitple Formulas to Absolute References

    Thank you DominicB, unfortunately this is just returning #VALUE!

  10. #10
    Registered User
    Join Date
    08-08-2019
    Location
    Surrey, England
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Convert Mulitple Formulas to Absolute References

    Sorry Pepe Le Mokko, I am using Excel 2016, I will update my profile.

  11. #11
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,603

    Re: Convert Mulitple Formulas to Absolute References

    Np and thanks, that will help members to better help you.

  12. #12
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Convert Mulitple Formulas to Absolute References

    Hi Lynnith

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  13. #13
    Registered User
    Join Date
    08-08-2019
    Location
    Surrey, England
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Convert Mulitple Formulas to Absolute References

    Thank you Dominicb, I think I have attached a basic version. If you go to the 'Master' sheet you can see the formula in columns C and D.
    Attached Files Attached Files

  14. #14
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Convert Mulitple Formulas to Absolute References

    Hi Lynnith

    Thanks for uploading the file. The formulae in the file you've provided are already absolute.
    Are these ones you've already converted as an example for us?
    • If yes, what did they look like before you converted these manually?
    • If no, then we may be talking at cross purposes : what do you mean by absolute references?


    HTH

    DominicB

  15. #15
    Registered User
    Join Date
    08-08-2019
    Location
    Surrey, England
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Convert Mulitple Formulas to Absolute References

    I was looking to place a $ in the second set of parentheses before the row number thinking this might help, but maybe not. My main problem is that I cannot sort the data on the 'Master' sheet without it knocking out the formulas. Any ideas would help.

  16. #16
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Convert Mulitple Formulas to Absolute References

    Hi Lynnith

    I've just checked the second set of parnentheses and it seem sthe macro does work - or at least, it worked for me.
    In your file I highlighted C2:D27 and went to Developer > Macros and it worked a treat.

    HTH

    DominicB

  17. #17
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Convert Mulitple Formulas to Absolute References

    Hi Lynnith

    Quote Originally Posted by Lynnith View Post
    ... I cannot sort the data on the 'Master' sheet without it knocking out the formulas ...
    To reproduce this problem on your example file, what range should I be sorting using what sort keys?

    HTH

    DominicB

  18. #18
    Registered User
    Join Date
    08-08-2019
    Location
    Surrey, England
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Convert Mulitple Formulas to Absolute References

    Hi dominicb

    If you just sort by Surname, for example sort Z to A. When I do this it is returning #N/A values

  19. #19
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Re: Convert Mulitple Formulas to Absolute References

    Hi Lynnith

    OK - red herring alert. Once I saw what was happening, I knew I'd seen it before and have been racking my brains why and how I fixed it.

    Excel's sort routine doesn't like formulae that reference themselves by sheet, so the formula =INDEX('Title 4'!$A$1:$D$5, MATCH('Master'!$A16 … in a sheet called Master will cause the issue.

    To fix this, in a copy of your workbook, go to Home > Find & Select, Replace and find Master! and leave replace blank.

    Hopefully, that should fix it.

    HTH

    DominicB

  20. #20
    Registered User
    Join Date
    08-08-2019
    Location
    Surrey, England
    MS-Off Ver
    Excel 2016
    Posts
    9

    Re: Convert Mulitple Formulas to Absolute References

    This is it!!! Thank you so much!

    Lynnith

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Change relative references in formulas (in an area) into absolute
    By marjattanb in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-09-2016, 05:16 PM
  2. How to for converting absolute references to relative references in formulas
    By edspyhill01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2014, 02:09 AM
  3. Adding absolute references ($) to formulas in multiple cells
    By riceguy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-30-2013, 01:24 AM
  4. Replies: 0
    Last Post: 11-15-2007, 02:39 AM
  5. How can I use non-absolute workbook/range references in formulas
    By rnabinger in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-19-2006, 12:10 PM
  6. [SOLVED] How to convert to absolute references including the sheetname?
    By Maria J-son in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2005, 05:06 PM
  7. [SOLVED] See code enclosed - Convert to formulas with absolute reference inculding the sheet references!
    By Maria J-son in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2005, 04:06 AM

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