[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?
