Problem with a link formula

[hmm]

I have a worksheet "a" that references cells on another worksheet "b". Cell
A3 in worksheet a finds row of the first cell in column F (worksheet "b")
containing (exactly matching) the string named "Field".

=MATCH(Field,'[Worksheet B.xls]Sheet1'!$F:$F,0)

Cells A4, A5, etc. in worksheet "a" finds the row of the next cell in column
F (worksheet b); that is, the next one after the row number found in the cell
above. In cell A4, the formula is:

=$A3+MATCH(Field,INDEX('[Worksheet
B.xls]Sheet1'!$F:$F,$A3+1):INDEX('[Worksheet B.xls]Sheet1'!$F:$F,65000),0)

This all works correctly on my PC. However, when my colleague opens the
worksheets on his PC, there are three problems:

1. Excel spells out the complete path in all formulas in worksheet "a", even
though worksheet "b" is open. (On my PC, only the file & book name are
spelled out, as above).

2. $F:$F is written $F:$F65536.

3. Most importantly, only the top formula, in cell A3, is evaluated
correctly. A4 and all cells below evaluate as the #REF! error.

(The name "Field" is evaluated correctly, so this is certainly not the
problem.)

We are both using Excel 2007. It is an .XLS worksheet (Excel '97-2003
format).

Can anyone help me fix the problem?

 

[hmm]

Thanks Jan.

Yes, both files are in the same folder.

 

[hmm]

Thanks for the tip Jan.

I did not have a chance to try it, because I had already converted the files
from XLS (2003 format) to XLSX (2007 format). So far, that solved the
problem.

 

[Pinot]

Hi,

I have searched all over for a solution to this issue which I thought may be
similar.. I hope we do not need to re-do...

We are using a Add-In from IBG Cognos PowerPlay. Version 7.4 is supposed to
be compatible with Excel 2007. We have a spreadsheet developed in Excel 2000
with Version 7.1 Powerplay Excel Add-In. Recently, we upgraded to Excel 2007.
We had to upgrade PowerPlay too.

When we first launch the spreadsheet (.xls since it was developed in Excel
2000), the cells containing formula "=PPQuery(GetWB()....." works. It
retrieves data from an OLAP cube and return information. When we try to save
it (be it Save or Save As), these cells then show #VALUE. And when I evaluate
the formula, GetWB() is returning error.

**If we connect to the OLAP cube (need not be the same cube. can by any
cube!) manually, these formula then work again. Once the file is saved, it's
back to #VALUE for all cells. We cannot possibly be repeating ** all the
time.

Tested PowerPlay 7.4 and Excel 2000 and no issue. Can anyone advise how to
resolve this ? This is a huge report with a lot of formula. It will be quite
a nightmare to re-do this.