Sort & add columns without messing up formulas
 

We hired a private party to do a cost study for us and the worksheet is over
1700 pages long. We need to be able to add a column for customer names and
sort data by those customer names or by part number, etc. However, we cannot
do any of that as it then throws everything off. Is it possible for us to
make changes to how the sheet was set up so that we can sort data and insert
columns without messing up the formulas? (Using Excel 2000)

Of course changes are possible.........almost anything can be done in Excel,
one way or another. As for specific advice, we really can't do that with
the information you've provided so far. If you would like to post back
(no attachments please) with an example of a formula that is being "messed
up" by the sorting, maybe we can help.......on the other hand, sometimes you
can just add a new column with sequential numbers in it that will represent
the present configuration of the data so all can be returned to normal by
just sorting on that new column after other sorts for analysis.

Vaya con Dios,
Chuck, CABGx3


"kate" wrote in message
...
We hired a private party to do a cost study for us and the worksheet is
over
1700 pages long. We need to be able to add a column for customer names
and
sort data by those customer names or by part number, etc. However, we
cannot
do any of that as it then throws everything off. Is it possible for us
to
make changes to how the sheet was set up so that we can sort data and
insert
columns without messing up the formulas? (Using Excel 2000)

Thank you for responding!

This is one of the formulas: =SUM($AH15:$AI15)



"CLR" wrote:

Of course changes are possible.........almost anything can be done in Excel,
one way or another. As for specific advice, we really can't do that with
the information you've provided so far. If you would like to post back
(no attachments please) with an example of a formula that is being "messed
up" by the sorting, maybe we can help.......on the other hand, sometimes you
can just add a new column with sequential numbers in it that will represent
the present configuration of the data so all can be returned to normal by
just sorting on that new column after other sorts for analysis.

Vaya con Dios,
Chuck, CABGx3


"kate" wrote in message
...
We hired a private party to do a cost study for us and the worksheet is
over
1700 pages long. We need to be able to add a column for customer names
and
sort data by those customer names or by part number, etc. However, we
cannot
do any of that as it then throws everything off. Is it possible for us
to
make changes to how the sheet was set up so that we can sort data and
insert
columns without messing up the formulas? (Using Excel 2000)

Hi Kate,
I'm afraid you've simply given a formula without any context or any
sense of what the problem is.

Without a clear description of the problem, we can only guess at
what the problem might be.

Perhaps if you go to my page on sorting, you will get a bit more
insight on sorting.

Before sorting you probably want to select
all of the columns as the primary failure in sorting properly is to
sort a column independently of data that belongs with that column.

The second biggest mistake is actually very similar and that is
allowing Excel to expand a single cell selection to the current
region not realizing that the current region stops at an empty
column and an empty row, so again you would sort data
and not carry all of the intended columns with your sort.

As for your formula =AH15 + AI15 is a bit shorter, but
the SUM formula will skip over cells with text and cells with
errors. A cell with a space is not an empty cell it has text
in it and frequently denotes an invalid attempt at clearing a cell..
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"kate" wrote in message ...
Thank you for responding!

This is one of the formulas: =SUM($AH15:$AI15)



"CLR" wrote:

Of course changes are possible.........almost anything can be done in Excel,
one way or another. As for specific advice, we really can't do that with
the information you've provided so far. If you would like to post back
(no attachments please) with an example of a formula that is being "messed
up" by the sorting, maybe we can help.......on the other hand, sometimes you
can just add a new column with sequential numbers in it that will represent
the present configuration of the data so all can be returned to normal by
just sorting on that new column after other sorts for analysis.

Vaya con Dios,
Chuck, CABGx3


"kate" wrote in message
...
We hired a private party to do a cost study for us and the worksheet is
over
1700 pages long. We need to be able to add a column for customer names
and
sort data by those customer names or by part number, etc. However, we
cannot
do any of that as it then throws everything off. Is it possible for us
to
make changes to how the sheet was set up so that we can sort data and
insert
columns without messing up the formulas? (Using Excel 2000)