Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to sort without losing individual set formulas?
I basically have 5 worksheets that I enter data - formulas are set for each
row and are different for each. The value I get for the formula is then transferred to a summary sheet. But if I sort any one of the 5 data sheets my formulas get all screwed up. Plus if I have to add or delete any rows the formulas again get screwed up. Is there any way to lock in the formulas. I tried protecting but that doesn't seem to do it. Help! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to sort without losing individual set formulas?
Nel post
*Rachael-R* ha scritto: I basically have 5 worksheets that I enter data - formulas are set for each row and are different for each. The value I get for the formula is then transferred to a summary sheet. But if I sort any one of the 5 data sheets my formulas get all screwed up. Plus if I have to add or delete any rows the formulas again get screwed up. Is there any way to lock in the formulas. I tried protecting but that doesn't seem to do it. Help! Hi Rachel, What do you mean with "screwed up"? Maybe you have some empy column beetwen the data you sort and the zone with formulas: for a good sorting you need no empty row/column in your list. -- (I'm not sure of names of menues, option and commands, because translating from the Italian version of Excel...) Hope I helped you. Thanks in advance for your feedback. Ciao Franz Verga from Italy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there a way to sort without losing individual set formulas?
Rachel, Have you tried modifying all your formulae to absolute references rather than relative ones? A "normal" formula most people use consists of relative references a simple example is in cell D5 "=A2-B3". The way Excel looks at this is to start from cell D5 where the formula is and work out where A2 and B3 are relative to D5, these are 3 up and 3 left (A2) and 2 up and 2 left (B3). By copying or sorting this, the relative location is preserved, but you will end up with the wrong answer because the formula is still going 3 up, 3 left and 2 up and 2 left. Write the same formula with the dollar sign infront of both letters and numbers in cell D5 =$A$2-$b$3 will fix the formula to ALWAYS go back to those cells regardless of where the formula is on the sheet - this is an absolute reference. Cheers Jon -- Jon Quixley ------------------------------------------------------------------------ Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803 View this thread: http://www.excelforum.com/showthread...hreadid=554379 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I sort multiple times | Excel Worksheet Functions | |||
Sort without messing up formulas | Excel Discussion (Misc queries) | |||
Select rows and sort based on type | 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) |