How to fix Compatibility error

[DocBrown]

I have a WB created and saved in Excel 2003 format. I use and edit the file
in Excel 2007 in compatibility mode at times. I need to keep it in 2003
format because some users are still on Excel 2003 and some are on 2007.

The WS are protected and have formulas and complex formatting and macros in
the WB. Sometimes in 2007, when I save it I get the Compatibilty error report
that points to certain cells ranges on the worksheets with this message:

This workbook contains data in cells outside of the row and column limit of
the selected file format. Data beyond 256 (IV) columns by 65,536 rows will
not be saved. Formula references to data in this region will return a #REF!
error.

One way I can cause this is to create a new blank ws. The cells called out
as having a compatibility problem have validation on a list. The list is a
dependent list with the following validation formula:

=OFFSET(INDIRECT(VLOOKUP(F13,LookupList,2,0)),0,0,COUNTA(INDIRECT(VLOOKUP(F13,LookupList,2,0)&"List")),1)

The VLOOKUP is pulling the Named Range from a lookup list to get the name of
the dependent list. The formula is built this way so that the drop down only
picks up the list cells that have data and ignores the blank cells at the
bottom of the named list range. F13 contains the value we want to find in the
lookuplist table.

Any ideas why this formula would trigger the compatibility warning? If I
save the file anyway, the warning is gone the next time I open and save the
file.

Thanks,
John

 

[Bob I]

Microsoft Office Compatibility Pack for Word, Excel, and PowerPoint 2007
file formats
By installing the Compatibility Pack along side of Microsoft Office XP,
or Office 2003, you will be able open, edit, save, and create files
using the Open XML Formats new to the 2007 Microsoft Office system.
Office 2000 users, as well as users of Windows 2000 SP4 and later, can
convert Open XML Formats to binary file formats from within Windows
Explorer.

http://office.microsoft.com/en-us/products/ha101686761033.aspx

 

[DocBrown]

Bob,

I think you totally missed the point of my post.

The file is in Excel 95-2003 format. I'm opening it in Excel 2007. I'm
keeping the file in Excel 95-2003 format. I have a complex worksheet with
macros and complex formulas and validation. I have a complex macro that is
used to add worksheets by copying a hidden template worksheet to a new
formatted visible one.

But if I add a new blank worksheet and save the file, Excel complains about
a compatiblilty issue with the validation formula on the formatted worksheet
that says that formulas are referencing cells ranges outside the Excel
95-2003 ranges.

But the WS work and save fine until I add a blank worksheet.

Anyone know what's up?
Thanks,
John

 

[Bob I]

Without spending a lot of time tracing your macros and figuring out how
they are causing the issue, it may be simpler to Save as a 2007 file and
move along.