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?

 

[Jan Karel Pieterse]

Hi Hmm,

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).

Are both files in the same folder?
If not, maybe their relative locations are not the same for you and your
colleague.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[hmm]

Thanks Jan.

Yes, both files are in the same folder.

 

[Jan Karel Pieterse]

Hi Hmm,

Yes, both files are in the same folder.

Odd, in that case things should work "out of the box".

If you go to the edit links dialog (Data, connections, edit links) and click
check status, what happens?
Maybe you need to change source?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[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.

 

[Jan Karel Pieterse]

Hi Hmm,

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.

I regularly see people having problems with Excel 2003 files on 2007 and vice
versa. Pity.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[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.

 

[Jan Karel Pieterse]

Hi Pinot,

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.

I think you might be better off contacting the manufacturer of PowerPlay about
this, seems to me a compatibility issue they need to solve.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com