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

Anonymous
2020-01-15T22:45:31+00:00

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, calculating with just the enter key stroke:

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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2020-01-16T17:07:04+00:00

RESOLVED!!

The IFS function simply wasn't supported by the version of EXCEL the file recipient was using. Rewrote the formula with IF and problem went away.

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-01-16T05:01:22+00:00

    Dear (Shawb),

    From your description, it seems that the curly brackets only appear you protect, hide cells, protect the sheet and send the attachment in an email. So when you see the curly brackets? Do you see them just after you hide cells or protect the sheet on your computer or in the email preview mode as you mentioned that the issue doesn’t happen after the recipient opens the Excel file on his computer?

    Does the issue only happens in the specific formula? If you insert another formula in another sheet, will the issue happen?

    Please first check whether you are using the latest version of the Office applications. If no, I suggest you update the version, restart the computer and then do a test to see the result. Meanwhile, upload a full screenshot of the Account page in the Excel application you are using for our reference. You can access the page via clicking on File>Account.

    Note: Remove any private information before uploading the screenshots.

    Meanwhile, I’d like you to provide the file link here for us to see if the issue is the Office applications’ version related. If you confirm the file can be shared publicly, welcome to add your confirmation and just provide the file link here.

    If you think that it can’t be shared publicly, you can provide the link in the private message I have sent to you.

    Best Regards,

    Cliff

    Was this answer helpful?

    0 comments