SOLVED

Excel formulas not working from PC to MAC

Copper Contributor

Hi, My boss developed an inventory spreadsheet for me to populate with data. She inputed the formulas as well. She uses a PC and I use a MAC.  The formulas are not working for me when I input the data, I am getting #NAME errors but when I email the populated reports back to her, the formulas are working on her computer.  Please help.  I can not do my job at this point.  I already went to help and ensured the data was in GENERAL and not TEXT. I also checked that the formulas are automatically updating. I attached the spreadsheet so you can directly review. Thank you!

17 Replies

@Nicole1243 

You didn't attach anything...

best response confirmed by allyreckerman (Microsoft)
Solution

@Nicole1243 

Thanks. the workbook uses the XLOOKUP function that is only available in Excel in recent versions of Microsoft 365. On a Mac or PC with Excel 2019 or earlier, XLOOKUP does not exist, so the formulas return #NAME!

 

In the attached version, I have replaced the XLOOKUP formulas with a version that uses VLOOKUP. That should work in all versions of Excel.

Thank You! It appears to be working. I appreciate you taking the time.

@Hans Vogelaar

Hi Hans, Hope you are well today. I had some spreadsheets work and others not using old formulas and new one you provided and inconsistencies within the same spreadsheet. If you have time can you please take a look at 4 spreadsheets and see if you can help. Thanks so much.

 

Nicole

@Nicole1243 

Hi Hans,

Sorry to bother you again with my excel issues. My boss had me upgrade microsoft/excel on my computer with the most current versions to address XLOOKUP formula issues. I thought running the reports today would have been simple. Instead I am running into formula challenges again but they are not consistent. On some of the reports they work 100% of the time, other reports some of the time and then none and I am using the same template! Can you please take a look at the attachments when you have a chance.  Thank you. Nicole

@Hans Vogelaar 

 

Hi Hans,

Sorry to bother you again with my excel issues. My boss had me upgrade microsoft/excel on my computer with the most current versions to address XLOOKUP formula issues. I thought running the reports today would have been simple. Instead I am running into formula challenges again but they are not consistent. On some of the reports they work 100% of the time, other reports some of the time and then none and I am using the same template! Can you please take a look at the attachments when you have a chance.  Thank you. Nicole

@Nicole1243 

I'm confused - why did you post 3 replies with attachments?

 

And where exactly is the problem?

Hi. The first two replies showed they were being sent to me not you which is why there were so many. Apologies. For all attachments please refer to Sheet1 which is where the formulas are located. The other sheets contain inputs that I pull from different sources. If you look at the Alchimie spreadsheet, for example, Sheet1 is not calculating formulas for columns C, E,F but is working for other columns containing formulas.

@Nicole1243 

Column F contains

=XLOOKUP(A2,Sheet2!A:A,Sheet2!H:H)/14

That should be

=XLOOKUP(A2,Sheet2!B:B,Sheet2!H:H)/14

since you're looking up the Child ASIN.

@Hans Vogelaar 

Thanks. I replaced the formula on Sheet 1, Column F to pull from Sheet 2, Column B (Child ASIN) and from Sheet 2, Column I (Units Ordered) and it still does not work. I attached revised spreadsheet.

@Nicole1243 

The formulas work in Excel 365 (if up-to-date), but some of the formulas return #N/A because the Child ASIN in column A on Sheet1 is not present in column B on Sheet2 and Sheet3.

The Child ASIN B002CO8900, for example, cannot be found on Sheet2 and Sheet3, but is present in cell B3 of Sheet4.

You could add IFERROR to suppress the errors, and use AVERAGE to calculate the averages. See the attached version.

@Hans Vogelaar 

Thank you for that new formula. Very helpful!

@Hans Vogelaar 

Good morning, it is Monday again and new data going into the reports  If you have a chance can you look at Sheet1 again and let me know why formulas not working. I don't think it is operator error this time. Thank you.

@Nicole1243 

It's the same problem as last time.

The Child ASIN B07JRBRNFW in B2 on Sheet1 does not occur in column B of Sheet2.

The Child ASIN B002CO8900 in B3 occurs neither on Sheet2 nor on Sheet3.

I suggested that you use IFERROR and you wrote that you liked that, but you haven't implemented it...

Thank you. I agree on feedback where Child ASINS do not exist, but for example. ASIN BO7JRBRNFW (Sheet 1, column A2) does exist in Sheet 3 but there is no run rate calculating in Column G2. In terms of IFERROR, my boss asked me to remove. Go figure!

@Nicole1243 

Strange: I get 1,71 in G2 (Excel Online uses comma as decimal separator in my country)...

 

S0675.png

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Nicole1243 

Thanks. the workbook uses the XLOOKUP function that is only available in Excel in recent versions of Microsoft 365. On a Mac or PC with Excel 2019 or earlier, XLOOKUP does not exist, so the formulas return #NAME!

 

In the attached version, I have replaced the XLOOKUP formulas with a version that uses VLOOKUP. That should work in all versions of Excel.

View solution in original post