very slow spreadsheet

[robonions]

Hi, i've got a very large spreadsheet for managing stock at our factory. I have two worksheets, one which records stock in and also gives a working stock value, and another worksheet which records used stock everytime a unit of stock is used in the factory. The sheet runs very slow and i'm sure it is because of the lookups which reference both sheets. My working stock sheet has over 10'000 rows and growing, and the used stock sheet over close to 30'000 rows. I have tried to use a dynamic named range but as yet had no success.

The formula which I think is causing the problem is:

=N9209-(SUMIF('Used Stock'!V:V,AB9209,'Used Stock'!O:O))

This basically references the opening stock figure, then subtracts the sum of the entries in the used stock sheet against the same stock code. Excel has to go through all 30'000 entries in the used stock sheet sum the figures.

Does anyone know of a way of speeding this up please?

 

[Fazza]

Hi,

It would be really good if you can post some sample data. And any other relevant further info.

SUMIF is pretty fast. Lookups which reference both sheets are not.

Non formula solutions might be possible. Such as a pivot table. Depends on your set up & requirements, hence my suggestion of sample data.

regards,

 

[robonions]

Hi,

I've cut a copy of the sheets right down and posted it here.

http://dl.dropbox.com/u/10167613/Stock Sheet test_sheet.xlsm

I'm experienced with pivot tables but they won't help here for what i'm doing i'm afraid.

 

[robonions]

hi, did anyone take a look at this? Any help would be greatly appreciated.

Thanks.

 

[Michael M]

Rob
A lot of users don't have Excel 2007 or 2010.
It might be easier if you used Excejeanie to post a screen shot of your data

 

[robonions]

what's Excejeanie?

 

[Matty]

Have a look here, Rob:

http://www.excel-jeanie-html.de/index.php?f=1

It allows you to post data into a message in a format that can then easily be copied and pasted into Excel by members to do testing and propose solutions.

Alternatively, you can just place borders around your data in Excel, hit copy, and then hit paste into the body of your message.

Matty

 

[Aladin Akyurek]

hi, did anyone take a look at this? Any help would be greatly appreciated.

Thanks.

Could you post some of the look up formulas that you have?