SOLVED
Home

Curly brackets added to formulas once workbook is copied and emailed out

%3CLINGO-SUB%20id%3D%22lingo-sub-1116215%22%20slang%3D%22en-US%22%3ERe%3A%20Curly%20brackets%20added%20to%20formulas%20once%20workbook%20is%20copied%20and%20emailed%20out%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1116215%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F520173%22%20target%3D%22_blank%22%3E%40shawb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20your%20version%20of%20Excel%20supports%20dynamic%20arrays%2C%20and%20on%20another%20PC%20is%20pre-DA%20version%20of%20Excel.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWrap%20your%20formula%20with%26nbsp%3B%40%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%40IFS(AND(...))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ewith%20that%20on%20pre-DA%20Excel%20it'll%20be%20opened%20as%20regular%20one%20(non-array%20formula).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1111428%22%20slang%3D%22en-US%22%3ECurly%20brackets%20added%20to%20formulas%20once%20workbook%20is%20copied%20and%20emailed%20out%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1111428%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3E%5BSOLVED%5D%20Hi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20having%20an%20issue%20where%20my%20formulas%20change%20after%20the%20workbook%20is%20opened%20on%20other%20PCs.%20For%20example%2C%20here's%20how%20a%20formula%20appears%20in%20my%20workbook%2C%20where%20I%20calculate%20with%20just%20the%20enter%20key%20stroke%2C%20no%20CSE%20ever%20used%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFS(AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C1%2C0%2C1%2C1)%3D%22Yes%22%2COFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C2%2C0%2C1%2C1)%3D%22Yes%22)%2C%22Both%22%2CAND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C1%2C0%2C1%2C1)%3D%22Yes%22%2COR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C2%2C0%2C1%2C1)%3D%22no%22%2COFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C2%2C0%2C1%2C1)%3D0))%2C%22Heated%20Only%22%2CAND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C2%2C0%2C1%2C1)%3D%22Yes%22%2COR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C1%2C0%2C1%2C1)%3D%22No%22%2COFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C1%2C0%2C1%2C1)%3D0))%2C%22Cooled%20Only%22%2CAND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C1%2C0%2C1%2C1)%3D0%2COFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C2%2C0%2C1%2C1)%3D0)%2C%22Fields%20left%20blank%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20same%20formula%20appears%20as%20follows%20after%20I%20protect%20and%20hide%20cells%2C%20protect%20sheet%2C%20save%2C%20copy%2C%20attach%20to%20email%2C%20send%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3DIFS(AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C1%2C0%2C1%2C1)%3D%22Yes%22%2COFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C2%2C0%2C1%2C1)%3D%22Yes%22)%2C%22Both%22%2CAND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C1%2C0%2C1%2C1)%3D%22Yes%22%2COR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C2%2C0%2C1%2C1)%3D%22no%22%2COFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C2%2C0%2C1%2C1)%3D0))%2C%22Heated%20Only%22%2CAND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C2%2C0%2C1%2C1)%3D%22Yes%22%2COR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C1%2C0%2C1%2C1)%3D%22No%22%2COFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C1%2C0%2C1%2C1)%3D0))%2C%22Cooled%20Only%22%2CAND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C1%2C0%2C1%2C1)%3D0%2COFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E%2417%2CFacility!E%2421%2CFacility!E%2425%2CFacility!E%2429%2CFacility!E%2433)%2CFacility!E%241%3AFacility!E%2433%2C0)%2CCOLUMN(E%241)%2C2%2C1%2C%22Facility%22))%2C2%2C0%2C1%2C1)%3D0)%2C%22Fields%20left%20blank%22)%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20do%20the%20curly%20brackets%20come%20from%3F%20It%20returns%20a%20VALUE!%20error%20because%20the%20formula%20does%20not%20compute%20with%20CSE%20shortcut...%20On%20the%20recipient's%20PC%2C%20as%20soon%20as%20you%20put%20the%20formula%20in%20edit%20mode%2C%20the%20curly%20brackets%20disappear%20and%20the%20computation%20happens%20normally.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EPlus%2C%20I%20don't%20think%20this%20formula%20can%20return%20an%20array%20given%20that%20I've%20populated%20the%20width%20and%20length%20returned%20by%20the%20offset%20function.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22%22%3EAny%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1111428%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

[SOLVED] Hi all,

 

I'm having an issue where my formulas change after the workbook is opened on other PCs. For example, here's how a formula appears in my workbook, where I calculate with just the enter key stroke, no CSE ever used:

 

 

=IFS(AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes"),"Both",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="no",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0)),"Heated Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="No",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0)),"Cooled Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0,OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0),"Fields left blank")

 

The same formula appears as follows after I protect and hide cells, protect sheet, save, copy, attach to email, send:

 

{=IFS(AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes"),"Both",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="no",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0)),"Heated Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)="Yes",OR(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)="No",OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0)),"Cooled Only",AND(OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),1,0,1,1)=0,OFFSET(INDIRECT(ADDRESS(MATCH(MAX(Facility!E$17,Facility!E$21,Facility!E$25,Facility!E$29,Facility!E$33),Facility!E$1:Facility!E$33,0),COLUMN(E$1),2,1,"Facility")),2,0,1,1)=0),"Fields left blank")}

 

Where do the curly brackets come from? It returns a VALUE! error because the formula does not compute with CSE shortcut... On the recipient's PC, as soon as you put the formula in edit mode, the curly brackets disappear and the computation happens normally.

 

Plus, I don't think this formula can return an array given that I've populated the width and length returned by the offset function. 

 

Any ideas?

1 Reply
Highlighted
Solution

@shawb 

It looks like your version of Excel supports dynamic arrays, and on another PC is pre-DA version of Excel.

 

Wrap your formula with @ like

=@IFS(AND(...))

with that on pre-DA Excel it'll be opened as regular one (non-array formula).

Related Conversations
I need the Formual...
alanaj in Excel on
1 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
CHALLENGE
marcialfigueroa in Excel on
0 Replies