LinkBack URL
About LinkBacks
In basic terms, what is the formula asking please?
=INDIRECT("AG"&MID(B7,FIND("$",B7,2),7))
In basic terms, what is the formula asking please?
=INDIRECT("AG"&MID(B7,FIND("$",B7,2),7))
Last edited by Marvo; 03-25-2024 at 05:31 AM.
You should be asking this in the thread where you were given the formula.
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.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
FIND("$",B7,2)
Find "$" in B7 starting at character 2 in the cell (in other words, ignore character 1). This retruns a number (the position of the character in the cell).
MID(B7,...,7))
Extract 7 characters from B7 starting at the position returned by the FIND function.
=INDIRECT("AG"&...)
Will be equal to cell AG followed by the value generated by the MID function.
=INDIRECT("AG"&MID(B7,FIND("$",B7,2),7))
If B7 contains Q$123456
Red it finds the position of the $ sign that occurs from the second character onwards. It then returns (up to) the next 7 charatcters (blue).
So it returns $123456. It then goes to cell AG$123456 and returns the value it finds there.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: Lá fhéile Pádraig sona dhaoibh
Thank you.
and if "AG" was on another sheet the formula can't work?
Not like that.
You'd need
=INDIRECT("'Sheet1'!AG"&MID(B7,FIND("$",B7,2),7))
No - you would neet to include the sheet name:
=INDIRECT("Sheet1!AG"&MID(B7,FIND("$",B7,2),7))
Thank you both. I'd so nearly got that myself, was going in the right direction. I thought "AG" was the problem, I'd tried =INDIRECT(All!"AG"&MID(B7,FIND("$",B7,2),7)) but hadn't realised I had to move the first ".
It's all well and good people giving you formulas but if you don't understand how they work then if something goes wrong or gets moved, you can't put it right and have to ask again. I do want to learn.
Learning is essential, otherwise you don't move forward. Glad to have helped again.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks