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))
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.
Hi,
use Modification->Replace $A2 to $A$2
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.
Yes, sorry I was wrong to write
Last edited by AliGW; 08-08-2019 at 04:35 AM. Reason: Please don't quote unnecessarily!
Thank you MrGes, unfortunately the reference is different on each row so am unable to use the find and replace function, but thank you.
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.
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
Thank you DominicB, unfortunately this is just returning #VALUE!
Sorry Pepe Le Mokko, I am using Excel 2016, I will update my profile.
Np and thanks, that will help members to better help you.
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.
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.
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
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.
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
Hi Lynnith
To reproduce this problem on your example file, what range should I be sorting using what sort keys?Originally Posted by Lynnith
HTH
DominicB
Hi dominicb
If you just sort by Surname, for example sort Z to A. When I do this it is returning #N/A values
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
This is it!!! Thank you so much!
Lynnith
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks