How to sort and keep formulas

[Upya]

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 :D

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

[Dave Peterson]

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 :D
>
> 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

[David McRitchie]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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 :D
>
> 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
>