SOLVED

Spill to display as blank

%3CLINGO-SUB%20id%3D%22lingo-sub-3090184%22%20slang%3D%22en-US%22%3ESpill%20to%20display%20as%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3090184%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20community%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20automate%20meeting%20dates%20dependent%20on%20a%20character%20(currently%20%22%5E%22)%20some%20rows%20will%20have%20this%20and%20another%20character%20(%22_%22)%2C%20some%20will%20have%20%22%5E%22%20and%20some%20rows%20will%20have%20%22_%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20%5E%20character%20does%20not%20exist%20I%20would%20like%20that%20cell%20to%20be%20blank.%20We%20have%20written%20an%20offset%20formula%20but%20cant%20help%20to%20avoid%20the%20dreaded%20spill.%20We%20are%20hoping%20to%20not%20need%20a%20coupling%20row%20for%20each%20phase%20item%20that%20calls%20another%20formula%20as%20excel%20beginners%20will%20be%20using%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethis%20is%20our%20current%20code%20and%20a%20screenshot%20of%20the%20file%20layout%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DOFFSET(INDEX(C%244%3AAO%244%2C%2CMIN(IF(C17%3AAO17%3D%22%5E%22%2CCOLUMN(C%244%3AAO%244))))%2C0%2C-2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Screen%20Shot%202022-01-30%20at%207.36.03%20PM.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F343746iDA15A15948DE4F93%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202022-01-30%20at%207.36.03%20PM.png%22%20alt%3D%22Screen%20Shot%202022-01-30%20at%207.36.03%20PM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you%20so%20much%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3090184%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3090717%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20to%20display%20as%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3090717%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1291756%22%20target%3D%22_blank%22%3E%40jcontois%3C%2FA%3E%26nbsp%3BNot%20sure%20why%20you%20use%20OFFSET.%20The%20%22%5E%22%20seems%20to%20occur%20only%20once%20on%20a%20row%20when%20it%20does.%20Would%20this%20one%20not%20work%20for%20you%3F%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24C%244%3A%24AO%244%2C%2CMATCH(%22%5E%22%2CC17%3AAO17%2C0))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3092473%22%20slang%3D%22en-US%22%3ERe%3A%20Spill%20to%20display%20as%20blank%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3092473%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BBless%20you.%20thank%20you%20so%20much!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello community, 

 

I am trying to automate meeting dates dependent on a character (currently "^") some rows will have this and another character ("_"), some will have "^" and some rows will have "_". 

 

If the ^ character does not exist I would like that cell to be blank. We have written an offset formula but cant help to avoid the dreaded spill. We are hoping to not need a coupling row for each phase item that calls another formula as excel beginners will be using this. 

 

this is our current code and a screenshot of the file layout 

 

 

=OFFSET(INDEX(C$4:AO$4,,MIN(IF(C17:AO17="^",COLUMN(C$4:AO$4)))),0,-2)

 

 

Screen Shot 2022-01-30 at 7.36.03 PM.png

 

 

 

 

 

thank you so much for your help. 

 

2 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

@jcontois Not sure why you use OFFSET. The "^" seems to occur only once on a row when it does. Would this one not work for you?

=IFERROR(INDEX($C$4:$AO$4,,MATCH("^",C17:AO17,0)),"")

 

@Riny_van_Eekelen Bless you. thank you so much!