SOLVED
Home

Indirect relative to the referenced cell

%3CLINGO-SUB%20id%3D%22lingo-sub-920077%22%20slang%3D%22en-US%22%3EIndirect%20relative%20to%20the%20referenced%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-920077%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20apologize%20if%20I%20posted%20this%20twice%2C%20the%20first%20time%20I%20wasn't%20sure%20if%20it%20posted%20or%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20been%20searching%20the%20net%20and%20reading%20articles%20for%20about%20a%20week%20now%20and%20can't%20find%20the%20answer%20to%20this...%20or%2C%20perhaps%20I%20did%20find%20the%20answer%20but%20didn't%20understand%20it.%20One%20never%20knows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20here's%20an%20example%20of%20what%20I'm%20trying%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20A1%20I%20want%20to%20use%20INDIRECT%20to%20reference%20another%20cell.%3C%2FP%3E%3CP%3ESo%20let's%20say%20INDIRECT(%22R%5B3%5DC%5B4%5D%22)%20but%20I%20want%20it%20referencing%26nbsp%3B%22R%5B3%5DC%5B4%5D%22%20from%20cell%20B5%20and%20not%20A1.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%2C%20your%20first%20thought%20is%2C%26nbsp%3B%20%22Why%20not%20just%20use%26nbsp%3BINDIRECT(%22R%5B8%5DC%5B9%5D%22)%20from%20A1%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20for%20what%20I'm%20trying%20to%20do%2C%20that%20would%20create%20a%20lot%20more%20work%20and%20I%20can't%20even%20begin%20to%20describe%20what%20I'm%20trying%20to%20do.%20I'm%20not%20an%20excel%20expert%20and%20my%20whole%20idea%20is%20confusing%20to%20me%20so%20I'm%20just%20doing%20a%20piece%20at%20a%20time.%20Lol.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20if%20this%20is%20possible%2C%20I'd%20greatly%20appreciate%20the%20help%20and%20if%20it's%20not%20possible%2C%20well%20I%20thank%20you%20for%20your%20time%2C%20just%20as%20well.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-920077%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-920330%22%20slang%3D%22en-US%22%3ERe%3A%20Indirect%20relative%20to%20the%20referenced%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-920330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F117433%22%20target%3D%22_blank%22%3E%40Amir%20Alvi%3C%2FA%3E%26nbsp%3BIt%20is%20a%20volatile%20function%20so%20it%20might%20slow%20down%20your%20worksheet%20if%20you%20have%2010%2C000%20of%20these%2C%20but%20how%20about%26nbsp%3B%3DINDIRECT(OFFSET(A1%2C3%2C4%2C1%2C1))%20in%20cell%20A1%3F%3C%2FP%3E%3CP%3EThe%20Offset%20function%20is%20moving%20from%20the%20current%20cell%20(A1)%2C%20down%203%20rows%2C%20over%204%20columns%2C%20and%20choosing%20a%20range%20that%20is%20one%20row%20by%20one%20column%20tall.%20If%20E4%20contains%20the%20test%20J1%2C%20then%20this%20formula%20will%20return%20the%20value%20from%20J1.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-920536%22%20slang%3D%22en-US%22%3ERe%3A%20Indirect%20relative%20to%20the%20referenced%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-920536%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F201398%22%20target%3D%22_blank%22%3E%40Bill%20Jelen%3C%2FA%3E%26nbsp%3BThank%20you%20for%20your%20response.%20Yes%2C%20because%20of%20it's%20volatility%20I%20didn't%20want%20to%20use%20the%20OFFSET%20function%20but%20since%20there%20have%20been%20no%20solutions%2C%20it%20seems%20I%20have%20no%20choice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20a%20little%20disappointing%20but%20after%20the%20time%20I%20spent%20trying%20to%20solve%20it%20with%20no%20luck%2C%20I'm%20actually%20not%20surprised%20it%20can't%20be%20done%20the%20way%20I'd%20like.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20worries.%20Thank%20you%20very%20much!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Amir Alvi
Occasional Contributor

Hi all, 

 

I apologize if I posted this twice, the first time I wasn't sure if it posted or not.

 

I've been searching the net and reading articles for about a week now and can't find the answer to this... or, perhaps I did find the answer but didn't understand it. One never knows.

 

Basically, here's an example of what I'm trying to do.

 

In A1 I want to use INDIRECT to reference another cell.

So let's say INDIRECT("R[3]C[4]") but I want it referencing "R[3]C[4]" from cell B5 and not A1.

 

I know, your first thought is,  "Why not just use INDIRECT("R[8]C[9]") from A1?

 

Well, for what I'm trying to do, that would create a lot more work and I can't even begin to describe what I'm trying to do. I'm not an excel expert and my whole idea is confusing to me so I'm just doing a piece at a time. Lol.

 

So, if this is possible, I'd greatly appreciate the help and if it's not possible, well I thank you for your time, just as well.

2 Replies
Solution

@Amir Alvi It is a volatile function so it might slow down your worksheet if you have 10,000 of these, but how about =INDIRECT(OFFSET(A1,3,4,1,1)) in cell A1?

The Offset function is moving from the current cell (A1), down 3 rows, over 4 columns, and choosing a range that is one row by one column tall. If E4 contains the test J1, then this formula will return the value from J1.

@Bill Jelen Thank you for your response. Yes, because of it's volatility I didn't want to use the OFFSET function but since there have been no solutions, it seems I have no choice.

 

It's a little disappointing but after the time I spent trying to solve it with no luck, I'm actually not surprised it can't be done the way I'd like.

 

No worries. Thank you very much!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies