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. 


Thread Tools  Display Modes 
#1




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




Maybe you could change that formula:
=b8*(if(a8="Upya",1,.9)) Do your best to keep those formulas consistentelse you'll be in real trouble. Another option. Insert a new columnCall 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 categoriesor 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




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  

