I can't sort data when the key column cells contain formula
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.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Redleg24" wrote in message
...
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!
--
Redleg24
"Dave Peterson" wrote:
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).
Redleg24 wrote:
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!
--
Dave Peterson
|