A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How to sort and keep formulas



 
 
Thread Tools Display Modes
  #1  
Old October 7th 05, 12:37 AM
Upya
external usenet poster
 
Posts: n/a
Default 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.

Many thanks for your help in advance


--
Upya
------------------------------------------------------------------------
Upya's Profile: http://www.excelforum.com/member.php...o&userid=27889
View this thread: http://www.excelforum.com/showthread...hreadid=473981

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

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.
>
> Many thanks for your help in advance
>
> --
> Upya
> ------------------------------------------------------------------------
> Upya's Profile: http://www.excelforum.com/member.php...o&userid=27889
> View this thread: http://www.excelforum.com/showthread...hreadid=473981


--

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

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.
>
> Many thanks for your help in advance
>
>
> --
> Upya
> ------------------------------------------------------------------------
> Upya's Profile: http://www.excelforum.com/member.php...o&userid=27889
> View this thread: http://www.excelforum.com/showthread...hreadid=473981
>



 




Thread Tools
Display Modes

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


All times are GMT +1. The time now is 06:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.