New Version Excel 2003 related question

I

Irshad Alam

I have MS office 2000 at home and Ms office 2003 at Office. Can I bring my
XLS file and use on Excel 2003 and after editing save or creating new file, I
can open them on Excel 2000.

Please advise.

Regards.

Irshad
 
B

Bryan Hessey

I have Office 97 and Office 2003 on the same PC at work, and office 2000
at home and frequently travel files (up to 100 worksheets per workbook)
between the 3, to date I have not had a problem, but am aware that some
features of the later editions may not revert back correctly to the '97
version. To date I have not encountered any.
 
D

Dave Peterson

Yep.

But there are new features in each version of excel (say the List feature that
was added in xl2003).

Things like that won't be available in xl2k.
 
B

Bryan Hessey

Hi Dave, and thanks for the 'List' - obviously a feature that I don't
use.

The only difference that I recall having an effect is the Date function
in the Text-to-Columns, where the '97 version gets the date format from
the first row, and the 2000 / 2003 versions allow you to specify for
any column.

My workbooks cover Cricket statistics (including the Print to Web
feature), Interest calculations, and my pie charts, and if those all
work there is hope that Irshad will not encounter any problems.

I guess the upgrade to me was something akin to the Word upgrade, many
new features, but why would I really need flashing sparkly text?
 
D

Dave Peterson

IIRC, xl97 allowed me to specify the date format I wanted for any field when I
did data|text to columns.

(And I don't use the List feature much, either.)
 
B

Bryan Hessey

Dave, yes, if you ran text-to-columns on data already in the sheet, bu
when run through the Wizard at File Open time was not possible, and th
Import converted the column to a dateof it's chosen format.
(I forget the problems we had trying to import some of our files int
a single column, but was not always possible)
 
D

Dave Peterson

If you were opening a .txt file via file|Open, you could still specify the date
format in that wizard.

If you were opening a .csv file, then you wouldn't even see the wizard.
 
A

Aladin Akyurek

Dave Peterson wrote:
[...]
(And I don't use the List feature much, either.)
Click to expand...
[...]

In fact, it's a significant "addition." Some of the important features
of the list functionality are:

[1] It largely eliminates the need for dynamic named ranges that
adversely affect the performance. Whenever the list expands, the
formulas referring to ranges within the list adjust automatically. This
facilitates contructing pivot tables and graphs which include new data
immediately upon refresh.

=COUNTIF(B4:B13,2)

which refers to a range in A3:D13, converted into a list with
Data|List|Create List, automatically adjusted as:

=COUNTIF(B4:B13,2)

after a new entry in A14.

[2] Copying down the formulas is fully automatic within the list itself.
One shortcoming in this respect is that the copying is not reliable when
a formula refers to a (changing) range above the formula cell:

Assuming that the following formula in B3

=IF((A3<>"")*ISNA(MATCH(A3,$A$2:A2,0)),SUMPRODUCT((A3>$A$3:$A$16)+0)+1,"")

is copied down to B16 in a list in the range A2:D16.

Upon a new entry in A17, we get in B17:

=IF((A17<>"")*ISNA(MATCH(A17,$A$2:A18,0)),SUMPRODUCT((A17>$A$3:$A$18)+0)+1,"")

which is obviously wrong.

This forces us to have in B3 something like:

=IF((A3<>"")*ISNA(MATCH(A3,$A$2:OFFSET(A3,-1,0),0)),SUMPRODUCT((A3>$A$3:$A$16)+0)+1,"")

[3] Provides AutoFilter, a Total Row whose results can be referred to in
audit formulas.
 
D

Dave Peterson

Lots of companies haven't upgraded to xl2003. So using relying on this feature
(if working on the file at home), could be a problem when it's taken back to
work.

Aladin said:
Dave Peterson wrote:
[...]
(And I don't use the List feature much, either.)
Click to expand...
[...]

In fact, it's a significant "addition." Some of the important features
of the list functionality are:

[1] It largely eliminates the need for dynamic named ranges that
adversely affect the performance. Whenever the list expands, the
formulas referring to ranges within the list adjust automatically. This
facilitates contructing pivot tables and graphs which include new data
immediately upon refresh.

=COUNTIF(B4:B13,2)

which refers to a range in A3:D13, converted into a list with
Data|List|Create List, automatically adjusted as:

=COUNTIF(B4:B13,2)

after a new entry in A14.

[2] Copying down the formulas is fully automatic within the list itself.
One shortcoming in this respect is that the copying is not reliable when
a formula refers to a (changing) range above the formula cell:

Assuming that the following formula in B3

=IF((A3<>"")*ISNA(MATCH(A3,$A$2:A2,0)),SUMPRODUCT((A3>$A$3:$A$16)+0)+1,"")

is copied down to B16 in a list in the range A2:D16.

Upon a new entry in A17, we get in B17:

=IF((A17<>"")*ISNA(MATCH(A17,$A$2:A18,0)),SUMPRODUCT((A17>$A$3:$A$18)+0)+1,"")

which is obviously wrong.

This forces us to have in B3 something like:

=IF((A3<>"")*ISNA(MATCH(A3,$A$2:OFFSET(A3,-1,0),0)),SUMPRODUCT((A3>$A$3:$A$16)+0)+1,"")

[3] Provides AutoFilter, a Total Row whose results can be referred to in
audit formulas.
Click to expand...
 
Top