It sounds like the named ranges may have become corrupted - check they're still there as it looks like Sage 300 can't find the data.  Saying that though, 99.9% of the time rather than worrying about the cause, simply create a fresh template and insert your data.  To do this, open an existing G/L Journal Batch in Sage 300, select your desired file format, set a file path and name, then export it with only the required fields expected i.e. those in your broken import template.  You can then delete the rows you just exported from the header and details sheets, and copy -> insert the rows from your broken template.  Now import your new template!

I'm not keen on creating a fresh template ... only to (perhaps) find the problem still occurs.

We are using Excel Power Pivot to extract data from a SQL database. So the 'import to Sage' excel spreadsheet has the following 4 tabs:

> Data ex SQL - this sheet has 2 pivot tables which have the Power Pivot data model as their source

> Journal_Headers - formulas linking to "Data ex SQL" sheet

> Journal_Details - formulas linking to "Data ex SQL" sheet

> Journal_Detail_Optional_Fields - empty sheet apart from row 1 titles

This import excel spreadsheet has worked fine for over 2 years ... until our move to Excel365 32-bit for refreshing/updating the spreadsheet (ready for source of Sage GL JE import).

I'm guessing that either the excel Power Pivot data model or the 2 pivot tables are treated differently by Excel365 ... and hence disrupting the Sage import process. 

When I did 'paste special values' to convert all the formulas to values on Journal_Headers and Journal_Details sheets, as well as deleting the 'Data ex SQL' sheet, I could then successfully import the file to Sage.

So will adopt the typical stripping out of extraneous material from the excel spreadsheet to help determine the 'tripping point' for the Sage import.   

Hi Andrew789 thanks for the update.  Its unfortunately you had to strip out the formulas as I agree that's not normally required.  As long as Sage 300 can find the required sheets, columns and data rows it should be fine.  I've also thought for years that it was a requirement to have a named range matching the sheet name that pointed to the column headers so the data could be located but I could be wrong - that could be a hangover from many years ago and I've just not updated my knowledge.  Cheers.