Problem with 2007 saved as 2003

[abe1952]

I have a 2007 worksheet that I save as a macro-enabled file in 2007. It has
many tabs, with about 15 tabs feeding into 5 intermediate tabs. The 5
intermediate tabs then feed into 1 tab that summarizes everything. There are
a few more tabs for other purposes within this same worksheet.

Before I can do these summaries, I export an expenditure report from our
accounting software, massage it to remove unnecessary rows and columns, and
do other things using one macro. This is one of the tabs in this worksheet.

After I'm done, I send it to one person who uses 2003. But before I send the
worksheet to this person, I delete the macro. Then I save the worksheet as a
2003.

The other person can open the file with his 2003 and everything looks
normal. But when he saves it, links show "#NAME?" Links are to various tabs
but all tabs are in this same worksheet; there are no links to another
worksheet.

The formula in those cells that show this error still look the same, e.g.,
in the Summary tab cell B74 the formula would look like
=SUM(Alaska:Idaho!B74), but the cells themselves show this error.

The functions I used in this worksheet are standard functions in 2003 and
earlier versions, such as SUM, VLOOKUP, LEFT, RIGHT, etc. I did not use any
functions only available in 2007.

What are possible solutions to this?

 

[JLatham]

I can't duplicate the problem in a very simple attempt to duplicate it: I
created a workbook (without a macro) in 2007 and set up 3 sheets named
Alaska, Deleware and Idaho. I put a value into B3 on each sheet, then on
Sheet1 I put
=SUM(Alaska:Idaho!B3)
and that gave me the correct sum.
I saved it first as a .xlsx file and then as a 97-2003 compatible .xls file.
I opened it in 2003 and it worked fine, I saved it from there and reopened
it and it still looks good and works properly.

You may have to examine each formula in the workbook that shows #NAME error
in them to determine which one is the initial culprit. Errors propagate,
that is, if a cell's formula is dependent on another cell with an error in
it, then that error will propagate on up the line to all formulas that
reference the original error cell or any intermediate ones along the way.

One thing to try is to run the Compatibility Checker at your (2007) end.
With your Excel 2007 file ready to be saved in 2003 format, use
Office Button --> Prepare --> Compatibility Checker
That will show you if there are any problems that could show up later (or it
should show them). In that same results dialog you can also choose to always
automatically run the compatibility checker on the file before saving so that
you don't have to remember to do so each time you save it as 97-2003 format.

 

[abe1952]

Thanks JL.

The suggestion to perform Run Compatibility Checker did the trick for me. It
revealed that in the 15 or so tabs which get data from the one export, I used
the SUMIFS function which, or course, does not work in 2003.

I modified my macro to copy those cells in place as values, and sure enough,
the whole thing now works as I intended.

SUMIFS is a neat new function, allowing me to do what I want in a simpler way.

Thanks again.