Replace OFFSET function in Structured Table

[SanjayGMusafir]

Hi Experts
For last many days I have been thinking about replacing offset function in a structured table "Banks". Thought of many things but couldn't narrow down to some suitable way to keep formula dynamic with structured reference.

The options I thought of -

• Just now I thought of TAKE function - For that I have to change cell reference to Cell Address
• Earlier, thought of INDEX function - Couldn't get the formula structure in my mind about - How to tell tell excel the row above

Finally, I came to this forum to rescue me.

Please help
Thanks in Advance

My current formula is -

=IFERROR(IFS([@Dt]="","",
AND([@Dt]<=TODAY(),
          OR([@Acc]<>OFFSET([@Acc],-1,0),
                    AND([@Acc]=OFFSET([@Acc],-1,0),[@D2]="Current")),
          [@D2]<>"Current",
          [@D2]<>"Reserve",
          [@T1]<>1),1,
AND([@Dt]=OFFSET([@Dt],-1,0),
          [@Dt]<=TODAY(),
          [@Acc]=OFFSET([@Acc],-1,0),
          [@D2]<>"Reserve",
          [@D2]<>"Current",
          OR([@T1]=OFFSET([@T1],-1,0),
                    [@T1]<>OFFSET([@T1],-1,0)+1)),1,
AND([@Dt]<="Today",[@T2]="Y"),2,
OR([@D2]="Current",
          AND([@D2]="Reserve",
                    [@Dt]=EOMONTH(TODAY(),0),
                    TODAY()=EOMONTH(TODAY(),0))),2,
[@D2]="Reserve",1,
AND([@Dt]<>"",[@Dt]<=TODAY()),0,
TRUE,""),2)

 

[RoryA]

Simplest solution is to just not use structured references for that.

 

[SanjayGMusafir]

Simplest solution is to just not use structured references for that.

The only challenge shall be, at it could be big one - Rows keep adding and deleting.

And without structured reference I have to keep checking column formula for errors.

Please advise

Thanks a lot

 

[RoryA]

Structured references have no effect on errors. It's the use of OFFSET that is helping avoid that. I don't know what the use case is but I can't really think of a good setup that involves blindly referring to the row above when you are adding and deleting rows. Why do you need to do that?

Also, in case you aren't aware, IFS evaluates every single argument passed to it at every calculation. Nested IF is more efficient.

 

[SanjayGMusafir]

Finally worked this out - Hope it helps someone who reaches this post.

Did "-6" because my table starts from Row 5

=IFERROR(IFS([@Dt]="","",
AND([@Dt]<=TODAY(),
          OR([@Acc]<>INDEX([Acc],ROW()-6),
                    AND([@Acc]=INDEX([Acc],ROW()-6),[@D2]="Current")),
          [@D2]<>"Current",
          [@D2]<>"Reserve",
          [@T1]<>1),1,
AND([@Dt]=INDEX([Dt],ROW()-6),
          [@Dt]<=TODAY(),
          [@Acc]=INDEX([Acc],ROW()-6),
          [@D2]<>"Reserve",
          [@D2]<>"Current",
          OR([@T1]=INDEX([T1],ROW()-6),
                    [@T1]<>INDEX([T1],ROW()-6)+1)),1,
AND([@Dt]<="Today",[@T2]="Y"),2,
OR([@D2]="Current",
          AND([@D2]="Reserve",
                    [@Dt]=EOMONTH(TODAY(),0),
                    TODAY()=EOMONTH(TODAY(),0))),2,
[@D2]="Reserve",1,
AND([@Dt]<>"",[@Dt]<=TODAY()),0,
TRUE,""),2)