Filtering w/ formulas?

**suzy** · 10-01-2008, 02:58 PM

How do I filter Ascending or Descending as sales change without moving the formulas in each cell and getting a DIV/0 error?

**ratcat** · 10-01-2008, 06:42 PM

G'day suzy,

What you need to do is in col E and F is to make the part of the formulas an absolute reference.

E5 = D5/$D$58 then copy drag the formula down

For Col F you must absolute the row numbers

=E$55+F$54

Note a quick way is to place your edit box at F6 then press Ctrl+H (Find and Replace)

Find what: E
Replace with: E$

Then click on 'Replace' until all the all the cell have been completed. Then do the same for F reference.

Next step is to enable 'Interactive calculations'. Tools > Options menu under Calculations tab.

I think I've covered everything

HTH

**suzy** · 10-03-2008, 12:06 PM

Thank you ratcat for your help! I am lost at the end though, I went to Tools > options to enable 'interactive calculations' I can't find it. I attached what I see but I don't understand? HELP Thanks!!!!!

**ratcat** · 10-03-2008, 05:13 PM

G'day Suzy,

Well its been a long time since I've looked at xl2003 and older versions.

In xl2007 they have a different way to get to the 'options' and they have changed the wording what box you have to tick. In 2007 to named 'Enable iterative calculation'.

I did some Research and found that the box you have to tick is just named 'Iteration'.

Click here then click on the 'Change the number of times Microsoft Excel iterates a formula'. This will give you more information.

HTH