Home

EXCEL IF Function with INDIRECT: need explanation for this Function

%3CLINGO-SUB%20id%3D%22lingo-sub-863917%22%20slang%3D%22en-US%22%3EEXCEL%20IF%20Function%20with%20INDIRECT%3A%20need%20explanation%20for%20this%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-863917%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20an%20explanation%20for%20this%20Function%3A%3C%2FP%3E%3CP%3E%3DIF(INDIRECT(ADDRESS(0%2C6%2C4%2C0%2C%22Summary%22)%2C%20FALSE)%3D%22%22%2C%20%22%22%2C%20INDIRECT(ADDRESS(0%2C6%2C4%2C0%2C%22Summary%22)%2C%20FALSE))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20add%20a%20data%20element%20in%20the%20%22Summary%22%20sheet%2C%20but%20this%20Function%20isn't%20picking%20it%20up.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-863917%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-864072%22%20slang%3D%22en-US%22%3ERe%3A%20EXCEL%20IF%20Function%20with%20INDIRECT%3A%20need%20explanation%20for%20this%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-864072%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F412050%22%20target%3D%22_blank%22%3E%40Mike9180304%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELast%20parameter%20in%20INDIRECT%20if%20FALSE%20means%20we%20use%20within%20formula%20R1C1%20notation.%3C%2FP%3E%0A%3CP%3EWithin%20address%20that's%20current%20row%20(0%20as%20first%20parameter)%2C%206th%20column%20(second%20parameter)%20using%20relative%20references%20(third%20parameter%3D4)%20using%20R1C1%20notation%20(5th%20parameter%3D0)%20on%20sheet%20Summary%20last%20parameter.%3C%2FP%3E%0A%3CP%3EOther%20words%20ADDRESS%20returns%20text%20%22Summary!RC%5B6%5D%22.%20INDIRECT%20converts%20it%20into%20real%20reference.%20Other%20words%20we%20take%20value%20from%20the%20cell%20in%20sheet%20Summary%20which%20is%20on%20the%20same%20row%20where%20the%20cell%20with%20formula%20and%20shifted%20on%206%20columns%20to%20the%20right.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20we%20enter%20formula%20in%20A1%20of%20our%20sheet%2C%20first%20INDIRECT%20returns%20value%20from%20Summary!G1.%20If%20formula%20in%20B3%20-%20when%20value%20from%20Summary!H3%2C%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Mike9180304
Occasional Visitor

I need an explanation for this Function:

=IF(INDIRECT(ADDRESS(0,6,4,0,"Summary"), FALSE)="", "", INDIRECT(ADDRESS(0,6,4,0,"Summary"), FALSE))

 

I am trying to add a data element in the "Summary" sheet, but this Function isn't picking it up.

1 Reply

@Mike9180304 

Last parameter in INDIRECT if FALSE means we use within formula R1C1 notation.

Within address that's current row (0 as first parameter), 6th column (second parameter) using relative references (third parameter=4) using R1C1 notation (5th parameter=0) on sheet Summary last parameter.

Other words ADDRESS returns text "Summary!RC[6]". INDIRECT converts it into real reference. Other words we take value from the cell in sheet Summary which is on the same row where the cell with formula and shifted on 6 columns to the right.

 

If we enter formula in A1 of our sheet, first INDIRECT returns value from Summary!G1. If formula in B3 - when value from Summary!H3, etc.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies