Formula which works in Excel 2003 doesn't in later versions?

[toveyj]

Hi

I have a spreadsheet which I built in Excel 2007 but saved in 2003 format.
Had to do this as some users who receive it still only have 2003 (believe it or not).
When opening the file in 2007 in Compatibility mode the formulas all work fine.
However, I have found if you open in Excel 2010 that some formulas don't work.
If I open the file in 2007 in Compatibility mode and save it as a 2007 xlsx file,
then open it in 2007 I get exactly the same incorrect result for the formulas
as I am getting when I open the 2003 version in 2010. So I am guessing that the formula
I am using doesn't work in 2007 and isn't supported by the 2010 compatibility mode.

Tried finding some information on what changes were made but it isn't that easy!

Here is an example of a formula that no longer works:

=IF($B11="","",IF(ISERROR(VLOOKUP($E$4&" "&$B11,'Ref Pivot'!$A$116:FL$9992,MATCH($E$5,'Ref Pivot'!$4:$4,0),FALSE)),0,VLOOKUP($E$4&" "&$B11,'Ref Pivot'!$A$116:FL$9992,MATCH($E$5,'Ref Pivot'!$4:$4,0),FALSE)))

Any ideas what the problem with this is?

Many Thanks

 

[theBardd]

You need to be a lot more specific in what you mean by doesn't work, and give some example data and expected results to help us figure any problems out. The formula in itself looks fine.

 

[toveyj]

Sorry I did try to add some more detail but 10 mins had expired and it wouldn't let me.

The formula gives a result of 0 as it stands. If I remove the ISERROR check then it comes back
with #N/A. So I am guessing it is nothing to do with the ISERROR check but something to
do with combining MATCH with VLOOKUP?

 

[toveyj]

The formula is using the contents of Cells E$4 & E11 on this sheet to lookup the correct row in a table on sheet 'Ref Pivot'.
It then uses MATCH of the value in Cell E5 to find which column to use in the range of information on 'Ref Pivot'.
The data on that sheet has a heading in Row 4, hence the MATCH against that row to work out the correct column.
I know the data is there and should match as this formula works fine in 2003.

 

[toveyj]

I've solved this now. It turns out that the data in the Ref Pivot sheet WAS incorrect when viewed in 2010.
It comes from a linked workbook. I had broken the links to that workbook before saving but instead
of just retaining the values it had retained the formula but without the reference
to the external workbook. So where the formula said =Workbook!Sheet1!A5, the formula had changed to =A5.
I have never seen this before so I can only image I made an error somewhere!
For some reason in 2003 and 2007 the value of the cells with these (now incorrect) formulas
were retained i.e. they still showed the value from the linked book. When opening in 2010 though
the values were showing as errors. So the original post was looking at the wrong problem.
I still don't understand why my linked formulas weren't completely removed when I broke the links
but at least I can fix it now!