Too many formulas causing slow process work book to save

**mjr10891** · 02-01-2013, 06:57 AM

Hello all,

i am working in service dealer company, as a office backup.

We have call regiseter in that month wise all complaints data,
i figured out how to count how many warranty calls,pw calls & amc calls 1 servce engineer was attended in one month
in another seet.

When i enter all service engineers, and complaints, its works good.

But my point is its all workbook process slower and its sometime hanging.

Can any one help to any loops or easy way to count how may calls in engineer wise in a month.

I hope u all guys understand me.

I am enclosing one excell sample work book for your reference..

Thanks in advance...

Mjr

**martindwilson** · 02-01-2013, 07:00 AM

1 try not to use whole column references a:a use a1:a1000

**Marcol** · 02-01-2013, 08:37 AM

With SUMPRODUCT() referring to whole columns in 2007 and above you are calculating 1,048,576 rows for each column!

Going a step further than Martin suggests, I would tend to use Dynamic Named Ranges to calculate exactly the data you need.

See this workbook for the names used.

Your formula in C2, Drag Across then Down, becomes

Formula:

Please Login or Register  to view this content.

I have had to remove your data validation lists, they were #REF! errors when I opened your file in 2003

As these lists weren't available, I changed B1 from "ARB (SM)" to "ARB" to get the correct matches

**mjr10891** · 02-02-2013, 12:51 AM

Thank you martin,

its so simple and its works...

But is there any thing to do with formulas if possible

thanks very much
mjr

**mjr10891** · 02-02-2013, 12:52 AM

Thank you marcol,

that's what i need change in formulas...

Once again thanks

mjr

**mjr10891** · 02-04-2013, 12:54 AM

Hi marcol,

when i copy this formula to another file its coming error.

Why and what is $ stands for....

Kindly help me

mjr

**FDibbins** · 02-04-2013, 02:18 AM

$ is used to absolute a cell reference, locking it so that when copied, it doesnt change.. it comes in 3 different "flavors...

$A$1 absolutes the entire cell reference, it will not chance when copied down or across
$A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across