I can't sort data when the key column cells contain formula

R

Redleg24

I am not able to sort a range of data where the "key" column conains ONLY
formula. While the computer screen displays the numerical result of the
formula in each cell of the column, the excel programming apparently can't
"read" the cell contents as a number in order to rearrange the rows in
ascending or decending order.

I'm an experienced excel user, however I've got a sneaking feeling that I'm
missing something a beginner could figure out!
 
D

Dave F

I've never had a problem sorting columns based on formulas.

What happens when you try to sort?
 
D

Dave Peterson

I bet you have a formula that looks like:
=sheet2!a1

A simple formula that refers to another worksheet.

Excel won't sort the way you want with these kinds of formulas. But you know
that (now).
 
R

Redleg24

The formula that won't sort is; =IF(B56=0,0,A7)
with the same relational formula repeated in approx. 30 more rows beneath.
The A7 result that is "returned" is a number as are the cell contents for A8
thru A38. So I get either a number or a zero in the cells as the result of
these formula. And, as I'm typing this, it occurred to me that I may have
formatted the cells incorrectly (General, Number, Currency, etc.) so I
checked and the cells are formatted > Number.
I'm stumped!
 
R

Ragdyer

Your cells *are* sorting!

BUT ... your using relative references, so ... as the cell contents
(formulas) are re-positioned, their references are changed *relative* to
their position (cell address).

Try changing 10 or so cells to absolute reference, and *then* sort those 10,
and see what happens.

Of course, you realize, if you made them absolute at the outset, you
wouldn't be able to copy them down the column and have them change
references in the first place.

You might try copying the formula column to a 'helper' column, and then
"Paste Special" - "Values", to remove the formulas and leave the data
behind, which you can then sort.
 
R

Redleg24

Thanks for the "eye opener"> Relative and absolute references was the issue.
I'm now trying to decide which of your approaches I want to use.

Have a great day!
 
R

RagDyer

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Redleg24 said:
Thanks for the "eye opener"> Relative and absolute references was the
issue.
I'm now trying to decide which of your approaches I want to use.

Have a great day!
Click to expand...
 
Top