SOLVED

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
Highlighted
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
Highlighted
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.

Highlighted

@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!