H
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?
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?