Excel XP to Excel 97

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,

I have the following function that works fine in Exel on my XP machine, but
not in Exel 97 ( I get a #Ref error). Any ideas?

=COUNTIF(INDIRECT( " ' " & " [Details.xls] " & $A $1 & " ' ! " & "
$D$5:$D$101 " ), B6)


Thanks
 
I have used similar formulas in Excel 97 and never had a problem. Check for
syntax errors and make sure the reference is correct. I assume the space
between $A $1 (and other elements in the formula) is something to do with
your mail program or making it easier to read and it doesn't appear that way
in the cell.

=COUNTIF(INDIRECT( "'" & "[Details.xls]" & $A$1 & "'!$D$5:$D$101"), B6)

should resolve to
Assuming A1 contains the text Sheet1 with no quotes

Countif('[Details.xls]Sheet1'!$D$5:$D$101,B6)

if you have such a workbook open and it contains a sheet1, then the formula
should work in Excel 97.

Regards,
Tom Ogilvy
 
Dave said:
Hi,

I have the following function that works fine in Exel on my XP machine, but
not in Exel 97 ( I get a #Ref error). Any ideas?

=COUNTIF(INDIRECT( " ' " & " [Details.xls] " & $A $1 & " ' ! " & "
$D$5:$D$101 " ), B6)


Thanks
Click to expand...

The various spaces in your formula cause the error (on my Excel97, anyway).
This works:
=COUNTIF(INDIRECT("'"&"[Details.xls]"&$A$1&"'!"&"$D$5:$D$101"),B6)
and so does this slightly simpler version:
=COUNTIF(INDIRECT("'[Details.xls]"&$A$1&"'!$D$5:$D$101"),B6)
as long as 'Details.xls' is open.
 
Hi,

Thanks for the help.

I found out what the problem was ( NB the spaces in the post were just for
clarity).

Excel XP dosnt mind if there are several instances of the program open - it
will still find the reference ok, however for Excel 97 the two worksheets
have to be open in the same program instance.

Does that make sense!!!

Thanks

Tom Ogilvy said:
I have used similar formulas in Excel 97 and never had a problem. Check for
syntax errors and make sure the reference is correct. I assume the space
between $A $1 (and other elements in the formula) is something to do with
your mail program or making it easier to read and it doesn't appear that way
in the cell.

=COUNTIF(INDIRECT( "'" & "[Details.xls]" & $A$1 & "'!$D$5:$D$101"), B6)

should resolve to
Assuming A1 contains the text Sheet1 with no quotes

Countif('[Details.xls]Sheet1'!$D$5:$D$101,B6)

if you have such a workbook open and it contains a sheet1, then the formula
should work in Excel 97.

Regards,
Tom Ogilvy


Dave said:
Hi,

I have the following function that works fine in Exel on my XP machine, but
not in Exel 97 ( I get a #Ref error). Any ideas?

=COUNTIF(INDIRECT( " ' " & " [Details.xls] " & $A $1 & " ' ! " & "
$D$5:$D$101 " ), B6)


Thanks
Click to expand...
Click to expand...
 
You sure you didn't have Tools|Options|view|Windows in taskbar checked and only
had one instance of excel running???

(xl2002 kept it as a #ref error if the workbooks were in separate instances for
me.)


Hi,

Thanks for the help.

I found out what the problem was ( NB the spaces in the post were just for
clarity).

Excel XP dosnt mind if there are several instances of the program open - it
will still find the reference ok, however for Excel 97 the two worksheets
have to be open in the same program instance.

Does that make sense!!!

Thanks

Tom Ogilvy said:
I have used similar formulas in Excel 97 and never had a problem. Check for
syntax errors and make sure the reference is correct. I assume the space
between $A $1 (and other elements in the formula) is something to do with
your mail program or making it easier to read and it doesn't appear that way
in the cell.

=COUNTIF(INDIRECT( "'" & "[Details.xls]" & $A$1 & "'!$D$5:$D$101"), B6)

should resolve to
Assuming A1 contains the text Sheet1 with no quotes

Countif('[Details.xls]Sheet1'!$D$5:$D$101,B6)

if you have such a workbook open and it contains a sheet1, then the formula
should work in Excel 97.

Regards,
Tom Ogilvy


Dave said:
Hi,

I have the following function that works fine in Exel on my XP machine, but
not in Exel 97 ( I get a #Ref error). Any ideas?

=COUNTIF(INDIRECT( " ' " & " [Details.xls] " & $A $1 & " ' ! " & "
$D$5:$D$101 " ), B6)


Thanks
Click to expand...
Click to expand...
Click to expand...
 
That would be hard to believe - particularly since indirect won't even work
with a closed workbook, much less going to a separate instance. I think
Dave Peterson suggested what you might have been doing.

Regards,
Tom Ogilvy

Dave said:
Hi,

Thanks for the help.

I found out what the problem was ( NB the spaces in the post were just for
clarity).

Excel XP dosnt mind if there are several instances of the program open - it
will still find the reference ok, however for Excel 97 the two worksheets
have to be open in the same program instance.

Does that make sense!!!

Thanks

Tom Ogilvy said:
I have used similar formulas in Excel 97 and never had a problem. Check for
syntax errors and make sure the reference is correct. I assume the space
between $A $1 (and other elements in the formula) is something to do with
your mail program or making it easier to read and it doesn't appear that way
in the cell.

=COUNTIF(INDIRECT( "'" & "[Details.xls]" & $A$1 & "'!$D$5:$D$101"), B6)

should resolve to
Assuming A1 contains the text Sheet1 with no quotes

Countif('[Details.xls]Sheet1'!$D$5:$D$101,B6)

if you have such a workbook open and it contains a sheet1, then the formula
should work in Excel 97.

Regards,
Tom Ogilvy


Dave said:
Hi,

I have the following function that works fine in Exel on my XP
Click to expand...
machine,
but
not in Exel 97 ( I get a #Ref error). Any ideas?

=COUNTIF(INDIRECT( " ' " & " [Details.xls] " & $A $1 & " ' ! " & "
$D$5:$D$101 " ), B6)


Thanks
Click to expand...
Click to expand...
Click to expand...
 
Can you have more than one instance of Excel97, then? If so, how? Does it
depend on which version of Windows you are using?
Click to expand...
...

At the moment I'm at a machine running Windows NT4 SP6 with Excel 97 SR-2. If I
perform [Windows] Start > Excel 3 times in succession, I get 3 TRUE instances of
Excel. By 'TRUE' I mean they appear as separate processes in Task Manager.
Multiple instances in the Task Bar isn't indicative, though I do get 3 instances
of Excel in NT's Task Bar by doing this.

Using VBA to call CreateObject("Excel.Application") also creates additional
separate instances of Excel. Those won't normally appear in the Task Bar, but
they will appear as separate processes in Task Manager.
 
Back
Top