Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort without messing up formulas
I have a spreadsheet where I would like to sort the results of a certain
column starting at the highest number down to the smallest number, when I do this using the Z to A button, the last column (which works out a percentage with a formula I have put in using the cells I wish to sort) gets messed up and just shows #DIV/0! I think this maybe because it is automatically sorting the total of the sorted column at the top of the spreadsheet after a sort instead of the bottom which is in turn messing up where the formula is looking to work out the percent. Hope this makes sense and someone can help Ruth |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort without messing up formulas
Hi Ruth,
What do your formulas look like, perhaps use of OFFSET Worksheet Function http://www.mvps.org/dmcritchie/excel/offset.htm or perhaps you are sorting a row you should not be sorting.- --- 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 "Ruth" wrote in message ... I have a spreadsheet where I would like to sort the results of a certain column starting at the highest number down to the smallest number, when I do this using the Z to A button, the last column (which works out a percentage with a formula I have put in using the cells I wish to sort) gets messed up and just shows #DIV/0! I think this maybe because it is automatically sorting the total of the sorted column at the top of the spreadsheet after a sort instead of the bottom which is in turn messing up where the formula is looking to work out the percent. Hope this makes sense and someone can help Ruth |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort without messing up formulas
Yes you were right I was sorting the totals row too and putting it at the top
as the largest figure in the column, which in turn messed up the formula which was pointing to the total row. I have fixed it by putting a blank row between the last row of figures and the totals row, cos excel kept including the totals row automatically but with a blank row it stopped doing that. Thanks for your help Ruth "David McRitchie" wrote: Hi Ruth, What do your formulas look like, perhaps use of OFFSET Worksheet Function http://www.mvps.org/dmcritchie/excel/offset.htm or perhaps you are sorting a row you should not be sorting.- --- 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 "Ruth" wrote in message ... I have a spreadsheet where I would like to sort the results of a certain column starting at the highest number down to the smallest number, when I do this using the Z to A button, the last column (which works out a percentage with a formula I have put in using the cells I wish to sort) gets messed up and just shows #DIV/0! I think this maybe because it is automatically sorting the total of the sorted column at the top of the spreadsheet after a sort instead of the bottom which is in turn messing up where the formula is looking to work out the percent. Hope this makes sense and someone can help Ruth |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort without messing up formulas
Hi Ruth,
Thanks for the verification. You do not need a blank row before the total, that is why I included a reference to http://www.mvps.org/dmcritchie/excel/offset.htm Or maybe you are using that blank row to separate the totals so you can use the current region Ctrl+Shift+* by selecting only a single cell -- the practice of sorting from a single cell selection is eventually going to mess up a spreadsheet big time if you have an empty column. http://www.mvps.org/dmcritchie/excel/sorting.htm l--- 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 "Ruth" wrote... Yes you were right I was sorting the totals row too and putting it at the top as the largest figure in the column, which in turn messed up the formula which was pointing to the total row. I have fixed it by putting a blank row between the last row of figures and the totals row, cos excel kept including the totals row automatically but with a blank row it stopped doing that. Thanks for your help Ruth "David McRitchie" wrote: Hi Ruth, What do your formulas look like, perhaps use of OFFSET Worksheet Function http://www.mvps.org/dmcritchie/excel/offset.htm or perhaps you are sorting a row you should not be sorting.- --- 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 "Ruth" wrote in message ... I have a spreadsheet where I would like to sort the results of a certain column starting at the highest number down to the smallest number, when I do this using the Z to A button, the last column (which works out a percentage with a formula I have put in using the cells I wish to sort) gets messed up and just shows #DIV/0! I think this maybe because it is automatically sorting the total of the sorted column at the top of the spreadsheet after a sort instead of the bottom which is in turn messing up where the formula is looking to work out the percent. Hope this makes sense and someone can help Ruth |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with named formula's | Excel Worksheet Functions | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |