How can I import an Excel spreadsheet with formulas intact and not converted to values?

MS Excel and Numbers may include functions with identical names in their list of supported functions, but they may implement these functions in different ways, preventing the formulas from being translated between applications.

If that's what is happening here, you'll need to rewrite the Excel table to avoid formulas that do not translate between Excel and Numbers.

Excel, for example supports the use of the R1C1 style as a cell reference. Numbers supports only A1 style.

Here's a test run:

I exported this small Number table to MS Excel format, then opened it using NeoOffice.

SUBSTITUTE, in column C survived the journey.INDIRECT, in D9, did not.

C2 and filled down: =SUBSTITUTE($B$2,"x",""&ROW()+12)

D9: =INDIRECT(D2,)

Repeating the process after editing out the comma in INDIRECT

D9: =INDIRECT(D2)

Both formulas were exported to the Excel file format, and survived intact into NeoOffice.

I edited the Substitute formula in Neo, changing 12 to 14, and added an INDIRECT formula in D8 referencing D3, then saved, and imported the file into Numbers:

As can be seen, the second document came back with a warning triangle. The message was:

The 5 in this cell is the last vvalue calculated by the formula (in Excel/NeoOffice).

Any change in that behaviour will have to come from a re-writing of the import function in Numbers as it relates to MS Excel. To request that, you'll need to talk to Apple directly, via the Feedback channel.

IOn Numbers, got to the Numbers menu and choose Provide numbers Feedback to make your request.

Regards,

Barry

Can you post a sample of the formulas? INDIRECT and SUBSTITUTE are valid functions in Numbers so I'm not sure what the problem is without seeing an actual formula. The only thing I can think of is if the formulas refer to other spreadsheets, something not supported by Numbers.

Starting with your last point, you really do not want to import Excel files, work on them in Numbers, then export them back to Excel. It creates a mess. Let's say you have an Excel spreadsheet with one worksheet called Worksheet1. Imported into Numbers that worksheet becomes Table 1 of sheet Worksheet1. Exported back to Excel it is now called Worksheet1 - Table 1. Imported back to Numbers it becomes Table 1 of sheet Worksheet1 - Table 1. Exported back to Excel it is now called Worksheet 1 - Table 1 - Table 1. See how it gets messy?

In your first example, the CELL function is not available in Numbers. It will not import or export this function.

In your second example, I don't know why it is not importing the INDIRECT function. From the warning message I think it is because Excel and Numbers cell references are not necessarily the same, they take a different form when referencing a different table/worksheet. Numbers apparently takes the worst case and says it isn't supported.

I do not think there is a substitute for CELL that would import/export. You might be able to create a substitute for use within Numbers but it would not export to Excel. The substitute would use ROW, COLUMN, and CHAR to get you the cell address. It would not get you the sheet and table names (or Excel worksheet name) or the filename, though. And because the worksheet name in Excel is not the same as the Sheet & Table names in Numbers, you can't use a constant value for that part. That's why it won't export/import.

With a redesign, you might be able to use OFFSET instead of INDIRECT. Instead of a cell address in cell A12, you would have a number for the offset from row 1. Instead of substituting "A" with "B" you would offset by a column. But if the table might be sorted and row 1 might get moved, OFFSET won't work.

Nonetheless, I think the first point about the downside of importing/exporting is the biggest factor here.