Subscribe Bookmark RSS Feed

Importing Excel spreadsheets containing formulae

Hi - I'm trying to import an Excel spreadsheet into JMP which has several worksheets, only one of which I'm actually interested in.  I know how to select the worksheet I want, but I have another problem in that the worksheet in question contains formulae with references to one or more of the other worksheets - and what seems to be happening is that JMP isn't always recognising the correct data types in the other worksheets to enable the formulae to be evaluated.  Some of them contain a mixture of both character strings and numerics, and JMP is (quite reasonably) interpreting these as character columns, turning any numerics it finds in the same column into character strings - which then prevents them from being used in the formulae that reference them.

The problem could possibly be solved if I could force the worksheet I want to import to be replaced by a "Copy | Paste Special | Values" version of itself when I import it.  Obviously I could do that in Excel, but I'd like to do it from JMP if possible.  Can that be done?

Alternatively I could try to override the default import column settings of some of the worksheets, so that for example worksheet A is forced to have all columns imported as numeric, whereas worksheet B of the same spreadsheet would be better handled by allowing JMP to take its best guess.  Can THAT be done?

Many thanks