Sorting absolute references

[Tim1217]

Hi,

I am not new to Excel but have just run across a never-before-encountered
issue.

If I have a formula in a cell that uses an absolute reference to another
cell, it appears the sort function will not work. Is that correct? When I
try to sort I get all kinds of strange new data.

 

[Frank Kabel]

Hi
are you sorting the source or the target range (in respect to your
formula)?

 

[RagDyer]

Does this old post shine any light on your problem.

http://tinyurl.com/ys5de
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi,

I am not new to Excel but have just run across a never-before-encountered
issue.

If I have a formula in a cell that uses an absolute reference to another
cell, it appears the sort function will not work. Is that correct? When I
try to sort I get all kinds of strange new data.

 

[Tim1217]

So does this mean for the sort to work correctly, all formulas must be
absolutes?

 

[RagDyer]

I can't think of every concievable formula and situation, but it's worth a
try on *copies* of your WSs, don't you think?
--


Regards,

RD

 

[Tim1217]

Ok, I get it now, no cells can contain formulas in the sort matrix. Thanks
professor.

But what a hassle if you want to copy large sections of a formula when
creating a WS. I now have to either write each formula seperately or write
the first with an absolute, copy it, and then go back and delete the $ signs
in the copies?

My WS has a column with total assets in a portfolio. There are 15 portfolios
total (the rows). So I set up columns for various asset classes (10 in
total) with a column next to that where I assign a certain % of total assets
to that asset class. A third colum for each asset class contains the formula
that multiplies the % allocation back to the total assets column. You can
see that I found it easier to just make the total asset column an absolute
in the formula when I copied across to each asset class section.

Anything I'm missing that might make the task easier or do I have to go the
tedious manual labor route (which could be prone to errors in formula
writing)?

Tim

 

[Tim1217]

Sorry, I meant to say; no cells can contain formulas "with absolute
references" in the sort matrix.

 

[JWolf]

I'm not really clear on what your problem is, but try this:
Do you know the difference between:
A1
$A$1
$A1
A$1
Because it sounds like you are copying your formulas accross columns
using $A$1 instead of $A1.

 

[RagDyeR]

If your sheet has *completed all calculations*, and all we're talking about
here is sorting for presentation or visual evaluation purposes, how about
first *copying*, and then *removing* all the formulas from the copy, leaving
strictly data behind to be sorted?

On a copy of the original, select the entire sheet (<Ctrl> <A>), right click
in the selection and choose "Copy",
Right click again, and choose "PasteSpecial",
Click on "Values", then <OK>.

You now have a sheet containing nothing but data, which you can sort to your
hearts content, without having unexpected data changes caused by dislocated
formula references.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Sorry, I meant to say; no cells can contain formulas "with absolute
references" in the sort matrix.