Sorting Data Without changing formulas

[Jonmo1]

Ok, I'm new here this is my first post.

I'm using just an example of my problem, not the real data of coarse.
Sheet 1 collumn A contains data manually entered by a user.
Sheet 2 collumn A has formulas referenceing Sheet 1 Collumn A
Sheet 1 collumn C has formulas referencing the results of the formulas in Sheet 2 Collumn A.

There are of coarse other data in collumns B,D,E etc in Sheet 1.
When I sort all collumns in Sheet 1, the formulas in Collumn C change with it. Since the formulas in Sheet 2 Collumn A do NOT get changed when sorting Sheet 1, this makes the formulas in Sheet 1 Collumn C invalid.

How can I correct this?

Any help would be great

 

[erik.van.geit]

Eric,
I am trying to understand the reasoning behind this. When you use the indirect and sort all columns in sheet 1, in column C, you get exactly what is in column A. Is this what was desired? I am not understanding the problem, but I want ot understand the solution. If you have the time of course.

check out INDIRECT in the helpfiles

put this formula in column A in some cells
=ROW()
you can sort the sheet as much as you want in any order any column ...
column A will always show the rownumbers

 

[RalphA]

To complement Eric's last post. When you do the sort, ALL cells on the same row are sorted, all together. But, then, the formula is activated, and, since the row number obtained by the ROW() function ALWAYS shows the same row, the answer comes back identical to the origingal answer, or data in that cell.

 

[Jonmo1]

xcellnoob,

The problem I was having was that there were formulas in sheet 1 that referred to results of formulas in sheet 2. Each formula referred to cells A1:A6 and they were numbered sequencially, the formula in row 2 sheet 1 referred to results of formulas in row 2 sheet 2. If I sorted all collumns in sheet 1, the formulas got messed up. The formula in row 2 sheet one would then refer to the results in sheet 2 row 7, making them invalid. The indirect function allowed the formulas in sheet 1 to not change the row # reference when sorted. In other works, the formula in row 2 sheet 1 will no ALWAYS refer to row 2 sheet 2, no matter how much sorting I do. It was actually a very cool function.

Thanks again Eric.

 

[Jonmo1]

Eric,

I noticed another nice side effect of the indirect function. And I plan to use this function frequently from now on because of this. Say you have formulas in sheet 1 referencing data in sheet 2. If you then delete say row 2 from sheet 2, the formula in row 2 sheet 1 now has the #REF where the cell reference was in the formula. Or if you Insert a row say between row 1 & 2 in sheet 2, the formula in row 2 sheet 1 now references row 3 sheet 2 (instead of row 2). That used to irritate the heck out of me. The inderect function eliminates that from happening. Very cool function. It's a little tricky to impliment, but once I got the hang of it, it's very handy.