If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 How to sort and keep formulas
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## How to sort and keep formulas

#1
October 7th 05, 12:37 AM
 Upya external usenet poster Posts: n/a
How to sort and keep formulas

I have another problem,

I am sure this one is easy to work out, but my mind seems to have gone
blank, so hopefully someone can help.

I have a worksheet which contains 3 columns.

Column A - Is a list of names
Column B - Is a cash amount
Column C - Is a cash amount from column B minus 10% (for example
=B8*0.9)

The cash amounts in B change regularly and I sort the table to show
which person has the most money next to their name (Sort by Column C
descending then by Column A ascending).

My problem is that one of the names in column A does not have 10% taken
from their cash, so the formula I use in their column C is simply =B8
(if their name is the 8th row down).

This works fine until I come to sort the table as mentioned above, the
formula for the person who doesnt have the 10% deducted does not follow
them, so depending on the amount they have in column B, they end up
having a figure minus 10% and the name now in the 8th row has their
column C not affected by the 10% deduction.

I hope thats understandable

I am sure there is a simply fix to this, I seem to remember something
about the use of \$ (like =\$B\$8) but that doesnt work.

--
Upya
------------------------------------------------------------------------
Upya's Profile: http://www.excelforum.com/member.php...o&userid=27889

#2
October 7th 05, 02:18 AM
 Dave Peterson external usenet poster Posts: n/a

Maybe you could change that formula:

=b8*(if(a8="Upya",1,.9))

Do your best to keep those formulas consistent--else you'll be in real trouble.

Another option.

Insert a new column--Call it percentage.

You could either type in the value (.9 or 1) for everyone or use a formula.

But then you could use:
=b8*d8

It might make it easier if/when others change categories--or the percentages
start to vary more.

Upya wrote:
>
> I have another problem,
>
> I am sure this one is easy to work out, but my mind seems to have gone
> blank, so hopefully someone can help.
>
> I have a worksheet which contains 3 columns.
>
> Column A - Is a list of names
> Column B - Is a cash amount
> Column C - Is a cash amount from column B minus 10% (for example
> =B8*0.9)
>
> The cash amounts in B change regularly and I sort the table to show
> which person has the most money next to their name (Sort by Column C
> descending then by Column A ascending).
>
> My problem is that one of the names in column A does not have 10% taken
> from their cash, so the formula I use in their column C is simply =B8
> (if their name is the 8th row down).
>
> This works fine until I come to sort the table as mentioned above, the
> formula for the person who doesnt have the 10% deducted does not follow
> them, so depending on the amount they have in column B, they end up
> having a figure minus 10% and the name now in the 8th row has their
> column C not affected by the 10% deduction.
>
> I hope thats understandable
>
> I am sure there is a simply fix to this, I seem to remember something
> about the use of \$ (like =\$B\$8) but that doesnt work.
>
>
> --
> Upya
> ------------------------------------------------------------------------
> Upya's Profile: http://www.excelforum.com/member.php...o&userid=27889

--

Dave Peterson
#3
October 7th 05, 03:12 AM
 David McRitchie external usenet poster Posts: n/a

Hi "Upya",
You are probably sorting on a single column rather than the
entire worksheet. Use Ctrl+Shift+Spacebar to select all cells
before doing your sort. (pre Excel 2003 you can use Slct+A).

http://www.mvps.org/dmcritchie/excel/sorting.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Upya" > wrote in message
...
>
> I have another problem,
>
> I am sure this one is easy to work out, but my mind seems to have gone
> blank, so hopefully someone can help.
>
> I have a worksheet which contains 3 columns.
>
> Column A - Is a list of names
> Column B - Is a cash amount
> Column C - Is a cash amount from column B minus 10% (for example
> =B8*0.9)
>
>
> The cash amounts in B change regularly and I sort the table to show
> which person has the most money next to their name (Sort by Column C
> descending then by Column A ascending).
>
>
> My problem is that one of the names in column A does not have 10% taken
> from their cash, so the formula I use in their column C is simply =B8
> (if their name is the 8th row down).
>
> This works fine until I come to sort the table as mentioned above, the
> formula for the person who doesnt have the 10% deducted does not follow
> them, so depending on the amount they have in column B, they end up
> having a figure minus 10% and the name now in the 8th row has their
> column C not affected by the 10% deduction.
>
> I hope thats understandable
>
> I am sure there is a simply fix to this, I seem to remember something
> about the use of \$ (like =\$B\$8) but that doesnt work.
>
>
>
> --
> Upya
> ------------------------------------------------------------------------
> Upya's Profile: http://www.excelforum.com/member.php...o&userid=27889
>

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

All times are GMT +1. The time now is 10:20 PM.