formulas won't maintain during sort -help please!
 

I have a rather large worksheet, columns to AN, rows to 369, and currently
transfers data across about 6 worksheets with more to come. I'm building a
price list with a long list a variables and then setting the prices into a
special format, so I cannot afford the data loss. When I sort my list for a
variety of reasons, the formula references do not maintain the original cell
references I need. I tried the absolute reference, bad idea. totally lost the
information. Had to rebuild. Tried partial absolute refernces, some success,
but a big loss on some.

Is there a good way to build a formula that will allow for cell references
to remain intact under various data sorts?
Thanks in advance!
John

I do not understand what you need, but check out the indirect() function in
Help.

=indirect("AA31") will always refer to AA31, no matter what you do to the
spreadsheet.

"MarcoPolo" wrote:

I have a rather large worksheet, columns to AN, rows to 369, and currently
transfers data across about 6 worksheets with more to come. I'm building a
price list with a long list a variables and then setting the prices into a
special format, so I cannot afford the data loss. When I sort my list for a
variety of reasons, the formula references do not maintain the original cell
references I need. I tried the absolute reference, bad idea. totally lost the
information. Had to rebuild. Tried partial absolute refernces, some success,
but a big loss on some.

Is there a good way to build a formula that will allow for cell references
to remain intact under various data sorts?
Thanks in advance!
John

Well, lets see. The rows 5 - 269 are for the bill of materials with a toiatal
in column X. Rows 274 through 426 have another bill of materials that affect
the totals in the first 265 rows. When I sort the second set, the totals in
the first set are affected. I can't live with this, checking each total
everytime I do any manipulations. I do have a common column, so whenI need to
find data I can do a cntl-F and get to my data quickly. Thanks for your help!
John

"bj" wrote:

I do not understand what you need, but check out the indirect() function in
Help.

=indirect("AA31") will always refer to AA31, no matter what you do to the
spreadsheet.

"MarcoPolo" wrote:

I have a rather large worksheet, columns to AN, rows to 369, and currently
transfers data across about 6 worksheets with more to come. I'm building a
price list with a long list a variables and then setting the prices into a
special format, so I cannot afford the data loss. When I sort my list for a
variety of reasons, the formula references do not maintain the original cell
references I need. I tried the absolute reference, bad idea. totally lost the
information. Had to rebuild. Tried partial absolute refernces, some success,
but a big loss on some.

Is there a good way to build a formula that will allow for cell references
to remain intact under various data sorts?
Thanks in advance!
John

When I change the formula to read as you specifiied, I lose all data. I have
a #VALUE error on all cells. Is there no way to kepp the cell referece data,
inside a formula, maintain that cell data integrity. The formula
=sum($a$5+$b$5+$c$5)*$w$1 does not allow me to sort the data by any particlar
column. The data that is in each of the specified cells, does not follow with
the sort procedure. Is there a solution to this problem? I tried removing the
absolute cell refences, but that didn't work either.
Any help would be appreciated.
John

"bj" wrote:

I do not understand what you need, but check out the indirect() function in
Help.

=indirect("AA31") will always refer to AA31, no matter what you do to the
spreadsheet.

"MarcoPolo" wrote:

I have a rather large worksheet, columns to AN, rows to 369, and currently
transfers data across about 6 worksheets with more to come. I'm building a
price list with a long list a variables and then setting the prices into a
special format, so I cannot afford the data loss. When I sort my list for a
variety of reasons, the formula references do not maintain the original cell
references I need. I tried the absolute reference, bad idea. totally lost the
information. Had to rebuild. Tried partial absolute refernces, some success,
but a big loss on some.

Is there a good way to build a formula that will allow for cell references
to remain intact under various data sorts?
Thanks in advance!
John