Excel 2007 sorting bug?

If an Excel 2007 sheet (#1)  has formula referenced cells to another sheet 
(#2),  and if the column in sheet (#2) containing the referenced cells is 
then A-Z sorted on the value then the original references in sheet #1 get 
lost as they are now pointing to different cells.

Is this an Excel 2007 bug?

Why after the sort does the relative position not move so keeping the 
correct references?.  I have not used $ in the cell address.

Beemer



0
Beemer (162)
7/13/2007 7:19:49 AM
excel 39879 articles. 2 followers. Follow

6 Replies
470 Views

Similar Articles

[PageSpeed] 20

No, this is not a bug.
If you have cells on Sheet1 pointing to other cells on Sheet2 with a 
direct reference like =Sheet2!B1, then if you change the contents of 
what is in those cells on Sheet2, by sorting sheet2, then Sheet1 will 
return (quite correctly) what is now in those cells.

If, on the other hand, you were using a Vlookup formula, then it would 
be possible to return the same value even after sorting Sheet2 e.g.

=VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)

-- 
Regards

Roger Govier


"Beemer" <Beemer@nowhere.com> wrote in message 
news:OgApz4RxHHA.4060@TK2MSFTNGP02.phx.gbl...
> If an Excel 2007 sheet (#1)  has formula referenced cells to another 
> sheet
> (#2),  and if the column in sheet (#2) containing the referenced cells 
> is
> then A-Z sorted on the value then the original references in sheet #1 
> get
> lost as they are now pointing to different cells.
>
> Is this an Excel 2007 bug?
>
> Why after the sort does the relative position not move so keeping the
> correct references?.  I have not used $ in the cell address.
>
> Beemer
>
>
> 


0
roger5293 (1125)
7/13/2007 7:49:58 AM
"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:%23oj3qJSxHHA.4184@TK2MSFTNGP06.phx.gbl...
| No, this is not a bug.
| If you have cells on Sheet1 pointing to other cells on Sheet2 with a
| direct reference like =Sheet2!B1, then if you change the contents of
| what is in those cells on Sheet2, by sorting sheet2, then Sheet1 will
| return (quite correctly) what is now in those cells.
|
| If, on the other hand, you were using a Vlookup formula, then it would
| be possible to return the same value even after sorting Sheet2 e.g.
|
| =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)
|
| -- 
| Regards
|
| Roger Govier
|
|
| "Beemer" <Beemer@nowhere.com> wrote in message
| news:OgApz4RxHHA.4060@TK2MSFTNGP02.phx.gbl...
| > If an Excel 2007 sheet (#1)  has formula referenced cells to another
| > sheet
| > (#2),  and if the column in sheet (#2) containing the referenced cells
| > is
| > then A-Z sorted on the value then the original references in sheet #1
| > get
| > lost as they are now pointing to different cells.
| >
| > Is this an Excel 2007 bug?
| >
| > Why after the sort does the relative position not move so keeping the
| > correct references?.  I have not used $ in the cell address.
| >
| > Beemer
| >
| >
| >
Roger,

I am not familiar with VLOOKUP but I tried to implement it using your 
formula.   I'm getting "value not available error".  I do not understand the 
last part of the formula :$B$100,2,0   Where does the "B100" come from and 
the "2" and "0"

Beemer




0
Beemer (162)
7/13/2007 10:06:36 PM
Hi

Take a look at Help on Vlookup.

The generalised form
=VLOOKUP(lookup_Value,table_range,offset,[True or False])
Basically, if you have a table of 2 columns of Data, A and B, Vlookup 
will search column A of the table, and return the value that is offset a 
given number of columns to the right.

In the example I posted, I just gave a supposed range of A1:B100 where 
the table existed. The 2 represented taking the value from column B, the 
second column in the table.
The 0 is the same as writing FALSE, as the 4 th argument tot he formula, 
which ensures it will only look for an exact match, not an approximate 
one.

I could equally (if I knew your data layout have said
=VLOOKUP("Roger",A:G,5,0)
which would have looked for the name Roger in column A, and, if found 
return the value from the same row that resides in column E

-- 
Regards

Roger Govier


"Beemer" <Beemer@nowhere.com> wrote in message 
news:e6aCWoZxHHA.1188@TK2MSFTNGP04.phx.gbl...
>
> "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:%23oj3qJSxHHA.4184@TK2MSFTNGP06.phx.gbl...
> | No, this is not a bug.
> | If you have cells on Sheet1 pointing to other cells on Sheet2 with a
> | direct reference like =Sheet2!B1, then if you change the contents of
> | what is in those cells on Sheet2, by sorting sheet2, then Sheet1 
> will
> | return (quite correctly) what is now in those cells.
> |
> | If, on the other hand, you were using a Vlookup formula, then it 
> would
> | be possible to return the same value even after sorting Sheet2 e.g.
> |
> | =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)
> |
> | -- 
> | Regards
> |
> | Roger Govier
> |
> |
> | "Beemer" <Beemer@nowhere.com> wrote in message
> | news:OgApz4RxHHA.4060@TK2MSFTNGP02.phx.gbl...
> | > If an Excel 2007 sheet (#1)  has formula referenced cells to 
> another
> | > sheet
> | > (#2),  and if the column in sheet (#2) containing the referenced 
> cells
> | > is
> | > then A-Z sorted on the value then the original references in sheet 
> #1
> | > get
> | > lost as they are now pointing to different cells.
> | >
> | > Is this an Excel 2007 bug?
> | >
> | > Why after the sort does the relative position not move so keeping 
> the
> | > correct references?.  I have not used $ in the cell address.
> | >
> | > Beemer
> | >
> | >
> | >
> Roger,
>
> I am not familiar with VLOOKUP but I tried to implement it using your
> formula.   I'm getting "value not available error".  I do not 
> understand the
> last part of the formula :$B$100,2,0   Where does the "B100" come from 
> and
> the "2" and "0"
>
> Beemer
>
>
>
> 


0
roger5293 (1125)
7/13/2007 10:22:24 PM
"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:ulFMPxZxHHA.4184@TK2MSFTNGP06.phx.gbl...
| Hi
|
| Take a look at Help on Vlookup.
|
| The generalised form
| =VLOOKUP(lookup_Value,table_range,offset,[True or False])
| Basically, if you have a table of 2 columns of Data, A and B, Vlookup
| will search column A of the table, and return the value that is offset a
| given number of columns to the right.
|
| In the example I posted, I just gave a supposed range of A1:B100 where
| the table existed. The 2 represented taking the value from column B, the
| second column in the table.
| The 0 is the same as writing FALSE, as the 4 th argument tot he formula,
| which ensures it will only look for an exact match, not an approximate
| one.
|
| I could equally (if I knew your data layout have said
| =VLOOKUP("Roger",A:G,5,0)
| which would have looked for the name Roger in column A, and, if found
| return the value from the same row that resides in column E
|
| -- 
| Regards
|
| Roger Govier
|
|
| "Beemer" <Beemer@nowhere.com> wrote in message
| news:e6aCWoZxHHA.1188@TK2MSFTNGP04.phx.gbl...
| >
| > "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
| > news:%23oj3qJSxHHA.4184@TK2MSFTNGP06.phx.gbl...
| > | No, this is not a bug.
| > | If you have cells on Sheet1 pointing to other cells on Sheet2 with a
| > | direct reference like =Sheet2!B1, then if you change the contents of
| > | what is in those cells on Sheet2, by sorting sheet2, then Sheet1
| > will
| > | return (quite correctly) what is now in those cells.
| > |
| > | If, on the other hand, you were using a Vlookup formula, then it
| > would
| > | be possible to return the same value even after sorting Sheet2 e.g.
| > |
| > | =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)
| > |
| > | -- 
| > | Regards
| > |
| > | Roger Govier
| > |
| > |
| > | "Beemer" <Beemer@nowhere.com> wrote in message
| > | news:OgApz4RxHHA.4060@TK2MSFTNGP02.phx.gbl...
| > | > If an Excel 2007 sheet (#1)  has formula referenced cells to
| > another
| > | > sheet
| > | > (#2),  and if the column in sheet (#2) containing the referenced
| > cells
| > | > is
| > | > then A-Z sorted on the value then the original references in sheet
| > #1
| > | > get
| > | > lost as they are now pointing to different cells.
| > | >
| > | > Is this an Excel 2007 bug?
| > | >
| > | > Why after the sort does the relative position not move so keeping
| > the
| > | > correct references?.  I have not used $ in the cell address.
| > | >
| > | > Beemer
| > | >
| > | >
| > | >
| > Roger,
| >
| > I am not familiar with VLOOKUP but I tried to implement it using your
| > formula.   I'm getting "value not available error".  I do not
| > understand the
| > last part of the formula :$B$100,2,0   Where does the "B100" come from
| > and
| > the "2" and "0"
| >
| > Beemer
| >
| >
| >
| >
Roger,

I don't think my requirement will fit with VLOOKUP.    Here is a different 
description of what I am trying to do:

I have a club syllabus to develop.  I collect the names and details of as 
many speakers I can find.  From this list I select who I want or who is 
available on a Thursday of the months Sept to May.  People change their 
minds about the date or drop out so I need to be able to link to others in 
the speaker list.

The layout is that one sheet contains a column of speaker names and its rows 
hold their details including topic.  The other sheet is the actual syllabus 
which will eventually be published.  So the syllabus sheet is a column of 
monthly Thursdays Sept to May.

The four columns of the speaker sheet are  A First Name  B Second Name  C 
Concatenated D Topic

e.g.

A                B                    C                    D
1 First            Second        Name                Topic
2 Mickey        Mouse         Mickey Mouse    Cats
|
|
|
50

The above keeps geeting new names added.

The Syllabus sheet contains:

A                    B                        C
1 Date            Name                    Topic
2  06/-9/2007    Mickey Mouse    Cats
|
|
|
37 15/05/2008

I start in the Syllabus sheet and enter the reference formula in B2 linking 
to C2 in the Names sheet.   All is well until I insert/delete a name or 
append to the end and then do a sort.   I am just a beginner with Excel and 
Access but I chose Excel to do the task as I did not know how to work with 
dates in Access.

Can you see any light in me acheiving a positive solution?

Beemer


















0
Beemer (162)
7/14/2007 7:25:33 AM
Hi

I think what you need to do is add an extra column (E) to the Speaker 
sheet with Date as the heading.
Just enter the proposed date of the speaking engagement in there.

On your Syllabus sheet in cell B2 enter
=INDEX(Speakers!$C$1:$C$100,MATCH(A2,Speakers!$E$1:$E$1000,0)))
in C2 enter
=INDEX(Speakers!$D$1:$D$100,MATCH(A2,Speakers!$E$1:$E$1000,0)))

You shouldn't need to do any sorting of the Speaker sheet, just amend 
the dates on Speaker sheet, and the relevant date will appear in your 
syllabus.


-- 
Regards

Roger Govier


"Beemer" <Beemer@nowhere.com> wrote in message 
news:uINUsgexHHA.3400@TK2MSFTNGP03.phx.gbl...
>
> "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:ulFMPxZxHHA.4184@TK2MSFTNGP06.phx.gbl...
> | Hi
> |
> | Take a look at Help on Vlookup.
> |
> | The generalised form
> | =VLOOKUP(lookup_Value,table_range,offset,[True or False])
> | Basically, if you have a table of 2 columns of Data, A and B, 
> Vlookup
> | will search column A of the table, and return the value that is 
> offset a
> | given number of columns to the right.
> |
> | In the example I posted, I just gave a supposed range of A1:B100 
> where
> | the table existed. The 2 represented taking the value from column B, 
> the
> | second column in the table.
> | The 0 is the same as writing FALSE, as the 4 th argument tot he 
> formula,
> | which ensures it will only look for an exact match, not an 
> approximate
> | one.
> |
> | I could equally (if I knew your data layout have said
> | =VLOOKUP("Roger",A:G,5,0)
> | which would have looked for the name Roger in column A, and, if 
> found
> | return the value from the same row that resides in column E
> |
> | -- 
> | Regards
> |
> | Roger Govier
> |
> |
> | "Beemer" <Beemer@nowhere.com> wrote in message
> | news:e6aCWoZxHHA.1188@TK2MSFTNGP04.phx.gbl...
> | >
> | > "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
> | > news:%23oj3qJSxHHA.4184@TK2MSFTNGP06.phx.gbl...
> | > | No, this is not a bug.
> | > | If you have cells on Sheet1 pointing to other cells on Sheet2 
> with a
> | > | direct reference like =Sheet2!B1, then if you change the 
> contents of
> | > | what is in those cells on Sheet2, by sorting sheet2, then Sheet1
> | > will
> | > | return (quite correctly) what is now in those cells.
> | > |
> | > | If, on the other hand, you were using a Vlookup formula, then it
> | > would
> | > | be possible to return the same value even after sorting Sheet2 
> e.g.
> | > |
> | > | =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)
> | > |
> | > | -- 
> | > | Regards
> | > |
> | > | Roger Govier
> | > |
> | > |
> | > | "Beemer" <Beemer@nowhere.com> wrote in message
> | > | news:OgApz4RxHHA.4060@TK2MSFTNGP02.phx.gbl...
> | > | > If an Excel 2007 sheet (#1)  has formula referenced cells to
> | > another
> | > | > sheet
> | > | > (#2),  and if the column in sheet (#2) containing the 
> referenced
> | > cells
> | > | > is
> | > | > then A-Z sorted on the value then the original references in 
> sheet
> | > #1
> | > | > get
> | > | > lost as they are now pointing to different cells.
> | > | >
> | > | > Is this an Excel 2007 bug?
> | > | >
> | > | > Why after the sort does the relative position not move so 
> keeping
> | > the
> | > | > correct references?.  I have not used $ in the cell address.
> | > | >
> | > | > Beemer
> | > | >
> | > | >
> | > | >
> | > Roger,
> | >
> | > I am not familiar with VLOOKUP but I tried to implement it using 
> your
> | > formula.   I'm getting "value not available error".  I do not
> | > understand the
> | > last part of the formula :$B$100,2,0   Where does the "B100" come 
> from
> | > and
> | > the "2" and "0"
> | >
> | > Beemer
> | >
> | >
> | >
> | >
> Roger,
>
> I don't think my requirement will fit with VLOOKUP.    Here is a 
> different
> description of what I am trying to do:
>
> I have a club syllabus to develop.  I collect the names and details of 
> as
> many speakers I can find.  From this list I select who I want or who 
> is
> available on a Thursday of the months Sept to May.  People change 
> their
> minds about the date or drop out so I need to be able to link to 
> others in
> the speaker list.
>
> The layout is that one sheet contains a column of speaker names and 
> its rows
> hold their details including topic.  The other sheet is the actual 
> syllabus
> which will eventually be published.  So the syllabus sheet is a column 
> of
> monthly Thursdays Sept to May.
>
> The four columns of the speaker sheet are  A First Name  B Second Name 
> C
> Concatenated D Topic
>
> e.g.
>
> A                B                    C                    D
> 1 First            Second        Name                Topic
> 2 Mickey        Mouse         Mickey Mouse    Cats
> |
> |
> |
> 50
>
> The above keeps geeting new names added.
>
> The Syllabus sheet contains:
>
> A                    B                        C
> 1 Date            Name                    Topic
> 2  06/-9/2007    Mickey Mouse    Cats
> |
> |
> |
> 37 15/05/2008
>
> I start in the Syllabus sheet and enter the reference formula in B2 
> linking
> to C2 in the Names sheet.   All is well until I insert/delete a name 
> or
> append to the end and then do a sort.   I am just a beginner with 
> Excel and
> Access but I chose Excel to do the task as I did not know how to work 
> with
> dates in Access.
>
> Can you see any light in me acheiving a positive solution?
>
> Beemer
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> 


0
roger5293 (1125)
7/14/2007 9:11:27 AM
Roger,

I shall top post as I note this is your preference.

My speaker list is already 60 people long and more will be added.  Having 
speakers appended means that without sorting I have to eye scan 
alphabetically to see if they are in that part of the column then eye scan 
the unsorted part of the column which is inconvenient and also not practical 
if at any time I have to let others work on the file.

I'm getting confused as I thought that Excel kept cells relative when moved 
(i.e. sorting names in the names sheet) except when $ was added in front or 
or or column or both address?

Beemer


"Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message 
news:eyqA5bfxHHA.4500@TK2MSFTNGP06.phx.gbl...
| Hi
|
| I think what you need to do is add an extra column (E) to the Speaker
| sheet with Date as the heading.
| Just enter the proposed date of the speaking engagement in there.
|
| On your Syllabus sheet in cell B2 enter
| =INDEX(Speakers!$C$1:$C$100,MATCH(A2,Speakers!$E$1:$E$1000,0)))
| in C2 enter
| =INDEX(Speakers!$D$1:$D$100,MATCH(A2,Speakers!$E$1:$E$1000,0)))
|
| You shouldn't need to do any sorting of the Speaker sheet, just amend
| the dates on Speaker sheet, and the relevant date will appear in your
| syllabus.
|
|
| -- 
| Regards
|
| Roger Govier
|
|
| "Beemer" <Beemer@nowhere.com> wrote in message
| news:uINUsgexHHA.3400@TK2MSFTNGP03.phx.gbl...
| >
| > "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
| > news:ulFMPxZxHHA.4184@TK2MSFTNGP06.phx.gbl...
| > | Hi
| > |
| > | Take a look at Help on Vlookup.
| > |
| > | The generalised form
| > | =VLOOKUP(lookup_Value,table_range,offset,[True or False])
| > | Basically, if you have a table of 2 columns of Data, A and B,
| > Vlookup
| > | will search column A of the table, and return the value that is
| > offset a
| > | given number of columns to the right.
| > |
| > | In the example I posted, I just gave a supposed range of A1:B100
| > where
| > | the table existed. The 2 represented taking the value from column B,
| > the
| > | second column in the table.
| > | The 0 is the same as writing FALSE, as the 4 th argument tot he
| > formula,
| > | which ensures it will only look for an exact match, not an
| > approximate
| > | one.
| > |
| > | I could equally (if I knew your data layout have said
| > | =VLOOKUP("Roger",A:G,5,0)
| > | which would have looked for the name Roger in column A, and, if
| > found
| > | return the value from the same row that resides in column E
| > |
| > | -- 
| > | Regards
| > |
| > | Roger Govier
| > |
| > |
| > | "Beemer" <Beemer@nowhere.com> wrote in message
| > | news:e6aCWoZxHHA.1188@TK2MSFTNGP04.phx.gbl...
| > | >
| > | > "Roger Govier" <roger@technologyNOSPAM4u.co.uk> wrote in message
| > | > news:%23oj3qJSxHHA.4184@TK2MSFTNGP06.phx.gbl...
| > | > | No, this is not a bug.
| > | > | If you have cells on Sheet1 pointing to other cells on Sheet2
| > with a
| > | > | direct reference like =Sheet2!B1, then if you change the
| > contents of
| > | > | what is in those cells on Sheet2, by sorting sheet2, then Sheet1
| > | > will
| > | > | return (quite correctly) what is now in those cells.
| > | > |
| > | > | If, on the other hand, you were using a Vlookup formula, then it
| > | > would
| > | > | be possible to return the same value even after sorting Sheet2
| > e.g.
| > | > |
| > | > | =VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)
| > | > |
| > | > | -- 
| > | > | Regards
| > | > |
| > | > | Roger Govier
| > | > |
| > | > |
| > | > | "Beemer" <Beemer@nowhere.com> wrote in message
| > | > | news:OgApz4RxHHA.4060@TK2MSFTNGP02.phx.gbl...
| > | > | > If an Excel 2007 sheet (#1)  has formula referenced cells to
| > | > another
| > | > | > sheet
| > | > | > (#2),  and if the column in sheet (#2) containing the
| > referenced
| > | > cells
| > | > | > is
| > | > | > then A-Z sorted on the value then the original references in
| > sheet
| > | > #1
| > | > | > get
| > | > | > lost as they are now pointing to different cells.
| > | > | >
| > | > | > Is this an Excel 2007 bug?
| > | > | >
| > | > | > Why after the sort does the relative position not move so
| > keeping
| > | > the
| > | > | > correct references?.  I have not used $ in the cell address.
| > | > | >
| > | > | > Beemer
| > | > | >
| > | > | >
| > | > | >
| > | > Roger,
| > | >
| > | > I am not familiar with VLOOKUP but I tried to implement it using
| > your
| > | > formula.   I'm getting "value not available error".  I do not
| > | > understand the
| > | > last part of the formula :$B$100,2,0   Where does the "B100" come
| > from
| > | > and
| > | > the "2" and "0"
| > | >
| > | > Beemer
| > | >
| > | >
| > | >
| > | >
| > Roger,
| >
| > I don't think my requirement will fit with VLOOKUP.    Here is a
| > different
| > description of what I am trying to do:
| >
| > I have a club syllabus to develop.  I collect the names and details of
| > as
| > many speakers I can find.  From this list I select who I want or who
| > is
| > available on a Thursday of the months Sept to May.  People change
| > their
| > minds about the date or drop out so I need to be able to link to
| > others in
| > the speaker list.
| >
| > The layout is that one sheet contains a column of speaker names and
| > its rows
| > hold their details including topic.  The other sheet is the actual
| > syllabus
| > which will eventually be published.  So the syllabus sheet is a column
| > of
| > monthly Thursdays Sept to May.
| >
| > The four columns of the speaker sheet are  A First Name  B Second Name
| > C
| > Concatenated D Topic
| >
| > e.g.
| >
| > A                B                    C                    D
| > 1 First            Second        Name                Topic
| > 2 Mickey        Mouse         Mickey Mouse    Cats
| > |
| > |
| > |
| > 50
| >
| > The above keeps geeting new names added.
| >
| > The Syllabus sheet contains:
| >
| > A                    B                        C
| > 1 Date            Name                    Topic
| > 2  06/-9/2007    Mickey Mouse    Cats
| > |
| > |
| > |
| > 37 15/05/2008
| >
| > I start in the Syllabus sheet and enter the reference formula in B2
| > linking
| > to C2 in the Names sheet.   All is well until I insert/delete a name
| > or
| > append to the end and then do a sort.   I am just a beginner with
| > Excel and
| > Access but I chose Excel to do the task as I did not know how to work
| > with
| > dates in Access.
| >
| > Can you see any light in me acheiving a positive solution?
| >
| > Beemer
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
|
|
| 


0
Beemer (162)
7/18/2007 7:17:07 AM
Reply:

Similar Artilces:

Outlook 2007 error when closing
Hi, Why am I getting this error when closing Outlook 2007. "Microsoft Office Outlook has encountered a problem and needs to close. We are sorry for the inconvenience" and it shows a check box "Restart Microsoft Office Outlook". I'm getting below details when clicking "What data does this error report contain?" Error Signature: Appname:outlook.exe Appver: 12.0.6514.5000 AppStamp: 4a89dc70 Modulename: dccmsp32.dll ModVer: 10.0.2009.929 ModStamp: 39d4133c fDebug: 0 Offset: 00005346 Please help. Thanks in ad...

How to open MS Excell 2007 Sheet in MS Excell 2000???
Hello, All! Is anybody know any converter for this trouble or how to . . .??? With best regards, Alek Luchnikov. E-mail: alekluch---FORSPAMERS---1983@mail.ru REMOVE ---FORSPAMERS--- Alek Luchnikov wrote: > Hello, All! > > Is anybody know any converter for this trouble or how to . . .??? > > With best regards, Alek Luchnikov. > E-mail: alekluch---FORSPAMERS---1983@mail.ru REMOVE ---FORSPAMERS--- > > Search for Microsoft Office Compatibility Pack. It should have prompted you to download it when you open the doc. Once it's installed you will be able to vi...

Excel '97
Is it possible to purchas an original install copy of Excel '97 anymore? Hi try ebay -- Regards Frank Kabel Frankfurt, Germany "Jeanne" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:003401c4f287$24f9e480$a401280a@phx.gbl... > Is it possible to purchas an original install copy of > Excel '97 anymore? I've tried Amazon and Ebay, and they both have UPGRADES available, but the original Office package wasn't part of the 97 OS was it? >-----Original Message----- >Hi >try ebay > >-- >Regards >Frank Kabel >F...

Excel 2003-provide feedback with sound
Trying to set in the options of MS Excel 2003 "provide feedback with sound", a message appears, that I have to download the Microsoft Sounds Add-in from the MS download page. But there is a hint, that this Add-in is only up to Excel XP and so it is not possible to install this feature. Is there any other version for MS Excel 2003? I installed Microsoft Office Sounds and it works in all versions from Excel 97 to Excel 2003. I didn't have to do anything special beyond selecting it under Options. So it "should" work. -- Jim Rech Excel MVP "Dirk" <Di...

Excel 2007 and Word 2007 Hangs when using the File -> Open command
I have a couple users that when they have Excel 2007 or Word 2007 open, and click the File -> Open option, and choose a file, it either takes about 3 minutes to open the file or the program will crash. If they were to double click on the documents or spreadsheets, the doc or sheet will open right up. This is happening on the user's Hard drive as well as network drives. Once you get a file opened in Word or Excel, however, the next time you go to File -> Open, it's really fast to open the file. ...

Excel to publisher import
How can I import an excel spreadsheet to publisher doc.? If you want to insert the actual spreadsheet, you can go to Insert > Object > Create from file and browse to the Excel file. If you want to merge the contents of the cells, you can use the Mail Merge feature in Publisher. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. "Bill" <Bill@discussions.microsoft.com> wrote in message news:80F32E78-1710-40E9-8922-CE4EBD69F5C2@microsoft.com... > How can I imp...

Export Access Record to Specific Cells in Excel
I'm looking for suggestions on how to export data from an Access table or Query into specific cells of a Worksheet. The table I'm importing will always contain a single record only. For example, I'd like to export data from Field1, field2...etc in an access table to Sheet2!A3, Sheet2!B3...etc. Any sugestions would be most appreciative. ...

2007 Public Folder Replicas
In my 2007 design I have two boxes with the HT/CAS roles installed and a CCR cluster (which doesn't support public folder replicas apparently). So where will my PF replicas live? Will this require yet another server? ...

Analysing data from several excel workbooks
Hi, I'm totally new to excel and i need to analyse the changes in a funding position across several years in a seperate excel sheet. As in, submissions are made year on year showing the amount of revenue spent on several different services. I need to be able to compare how those have moved over the last 5 years in a single spreadsheet. I appreciate any help. Thanks To give a clear answer we would need a bit more detail of the data layout Give us a simplified version of what the data looks like Your 'subject' talks about different workBOOKS but the text of the ...

Too Many Excel Apps running
I recently installed Excel 2010. My operating system is XP Pro. Ever since I installed Excel 2010 I find that every time I open an Excel file it opens a new Excel. It is not unusual for me to have 5 different Excels open even after I close a file. My bottom ribbon is full of Excels! I previously used Excel 2003 and did not have this problem. I know these are different Excels operating because I am unable to open a split window to show two different files. Note - I usually open my files via Explorer. Is there a setting that I can use to require that my single open Excel can open any new Exce...

Excel document changes format
Hi, I have an Excel document that changes format unexpectedly. It seems to change the format of the document eventhough i saved it. I have to resize the windows of the bar graphs, etc. It just doesn't save it the way i want. I re-installed MSOffice and re-did the NT profile, but still persists. Any ideas? Thanks. Excel does seem to have problems if the screen zoom is anything othe than 100%. Usually changing to 100 and back resets he screen display -- Message posted from http://www.ExcelForum.com ...

Sort Worksheets #2
How do I sort sheets in a workbook, please? Thanks! Check out http://cpearson.com/excel/sortws.htm In article <e02c01c43c07$5afeeb70$a601280a@phx.gbl>, "Manhar" <anonymous@discussions.microsoft.com> wrote: > How do I sort sheets in a workbook, please? Thanks! Hi see http://www.cpearson.com/excel/sortws.htm >-----Original Message----- >How do I sort sheets in a workbook, please? Thanks! >. > ...

Linking Word and Excel #2
I have a Word document with a large number of tables linked to cells in an Excel spreadsheet. The tables in the worksheet are in the same order as those in the Word document. I would like to insert a new table between other tables linked to the Excel spreadsheet but when I insert the new table between the others in the Excel spreadsheet, it invalidates all the other linked information. I take this to mean that the links are not absolute references. Is there any way to do this without having to reconstruct the links? Thanks ...

can you date time stamp entries in excel
is it possible that when someone puts an entry on a shared worksheet in excel it can automatically date and time stamp their entry. Private Sub Worksheet_Change(By Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then With Target .Offset(0, 1).Value = Format(Now, "dd mmm yyy hh:mm") End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate ...

Excell 2007 messing up graphics
I have an invoice Excel sheet setup with my company logo on it. For some reason Excel 2007 messes it up during printing. It prints it about 10 times the size. I tried to re create the work sheet and it still will not print the graphic. It show it ok but will not print it. When I go to Print Preview the logo (JPG graphing) is not seen at all. Any Ideas what is going on? ...

Outlook 2007 #34
I have a Toshiba laptop running XP. I replaced Office for Teacher and Student 2003 with Office Home and Student 2007 and replaced Outlook 2002 with Outlook 2007. The problems I was having with Outlook 2002 were as follows: The incoming emails would repete 5-10 times upon loading on my computer. The status would just keep showing numbers 50%,25%,8%,0%,2% etc.....in no logical order or sequence. Messages I was trying to send would just stay in the out box. When you first open Outlook 2002 it would say that it was not closed properly and would look for errors (even though it was closed p...

How can I customize the right-click context menu in Word 2007
I use cut & paste of imported text a lot in both Word 2007 and Excel 2007. In Excel the right-click context menu includes Paste Special; in Word it does not. How can I add Paste Special to the Word context menu? See http://gregmaxey.mvps.org/Customize_Word2007_SC_Menu_Programatically.htm -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid professional basis. "Steve Samson" <SteveSamson@discussions.microsoft.com> wrote in message news:A20FD91E-CFE2-402A-8A54-9F58880A3126@micros...

how do i report a bug in excel
how do i report a bug in excel Hi Dave well if you'ld like to post here what you've found we can (probably) tell you if it is a known one already and maybe even suggest a solution or workaround. Cheers JulieD "dave" <dave@discussions.microsoft.com> wrote in message news:DF36F937-24C7-4E2C-A4FF-D743A4E9AAC0@microsoft.com... > how do i report a bug in excel Or, as is often the case, not a bug at all but "just the way things work". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "JulieD&q...

Excel & PDF
With Excel 2000 there was a PDF conversion function. Now I have Excel 2003 and there is no PDF converter. Is this right? I don't have a PDF conversion at work (xl2k). You sure your conversion software didn't come separate and installed itself as an option in excel (maybe with your Scanner/OCR???). Bob Purcell wrote: > > With Excel 2000 there was a PDF conversion function. Now > I have Excel 2003 and there is no PDF converter. Is this > right? -- Dave Peterson ec35720@msn.com ...

How do I change the inverse colour in an Excel chart?
I want to have blue bars when the values are positive and yellow when they are negative. I have checked the box for "invert if negative" when choosing the colour in Format Data Series, but I still get blue for both. David, The concept described here should work: http://www.pdbook.com/index.php/excel/conditional_column_chart/ ---- Regards, John Mansfield http://www.pdbook.com "david_hutton.NO_SPAM" wrote: > I want to have blue bars when the values are positive and yellow when they > are negative. I have checked the box for "invert if negative" wh...

open excel 2007 refresh query from batch file
Hello all, I have a simple script saved as "C:\refresh_excel.vbs" that opens an Excel file, and does a refresh for a query on Sheet 1. I created a scheduled task to run this every day. It worked fine for previous versions of Excel, but since I have upgraded to Excel 2007 it doesn't work. Any help it helping me figure this out would be appreciated. /*contents of refresh_excel.vbs*/ Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True Set objWorkBook = objExcel.Workbooks.Open("C:\reports\UPS.xls") objWorkbook.Sheets("...

Hidding rows when sorting
Hi all I have a sheet, where I upload informaton - some rows are hidden (parts already in stock) So, when they sort by some coloumn, it shows all rows, and hides the one which does not match. Is there an event I can catch so I can hide even more? It is a file someone else made at some time, so: how do I add the sort option? WBR Sonnich "hides the one which does not match" Sounds like you are filtering, not sorting. Gord Dibben MS Excel MVP On Tue, 1 Jun 2010 09:46:57 -0700 (PDT), jodleren <sonnich@hot.ee> wrote: >Hi all > >I have a s...

Publisher 2007 Error, Windows Vista
I have an issue using Publisher 2007 on Windows Vista 32bit. When attempting to print (or print preview) the user interface locks up and my only option is to ctl-alt-del. I cannot switch windows, I cannot bring up task manager. The UI still displays updates (outside of Publisher), but I cannot "use" any of the interface except the Windows Security menu via ctl-alt-del. If I attempt a logout I can force Publisher to stop. This is followed by two logged events: --- 1. Source: Office 12, Event ID 5000 The description for Event ID 5000 from source Microsoft Office 12 cannot be ...

Project Server 2007
I have a clean install of PS2007 - SP2 on WSS in a small farm environment - DB server on another box. My installation "mostly" works, however, I am receiving an "unknown error" message when I try to edit the RBS. The item gets checked out and the "unknown error" message appears on the top of the page with only the Cancel button available. I've checked the logs and don't see anything obvious, the event logs on both machines aren't showing anything and I'm fairly stumped. Any insight, ideas, things to try would be greatly appreciated. Thanks in adv...

excel #18
I have works 6.0 instaled on a xp system but i want to upgrade to excel because i have save document as save type formated text(space delimited)(*prn)and works does not have this format. First, I don't use MSWorks. But if that's the only reason for your upgrade, you might want to try this to see if it works in MSWorks: In excel, you can concatenate a bunch of cells into one cell. You could use the =concatenate() function or the & operator: =a1&b1&c1 will join a1, b1, c1 in one cell. But if you're shooting for .prn file (fixed width), you could see if you have t...