Code to calculate

[Jonsson]

Hi,

I have a workbook that containing several (106) similar sheets. It´
the same calculations at the same place in every sheet.

Today I have formulas in the cells, but the workbook is to big (10MB)


Is there a way to put a code in the sheets VBA, and by this get th
same calculation in specific range? It´s always the same calculation
and the same range

Will it decrease the size of the WB, using code calculations, instea
of cellformulas?

Example of one calculation:
=IF(AND(B4<=20;C4>20);(C4-20)*0,7;IF(B4>20;(C4-B4)*0,7;"0,00"))

Hope someone can lead me on the right track!!

//Thoma

 

[BrianB]

It may help to have calculation set to manual and just calculate th
range that you want. eg.


Code

 

[Jonsson]

Hi Brian

So you dont think it will work my way?

To set calculation to manual may wont work, as I have users that ar
not so good at excel.

Therefore a code should be best, if possible!!??

//Thoma

 

[Guest]

If the layout of all 106 sheets are the same (same cell location, formatting, etc.), then the most efficient way to tackle this problem is to build 2 sheets:

Sheet 1 - Raw data table: Contains raw in a database format so sheet 2 can look up data easily

Sheet 2 - Summary: Contains headings, titles, notes and a bunch of "lookup" functions that gathers data from Sheet 2. There should be a cell in Sheet 2 that houses the lookup reference (preferrable as a pull down menu, say data validation).

If you can do it by this method, you could save 104 sheets. Also it is much easier to mange data this way (e.g. if you want to change a formula on all 106 sheets).

 

[Jonsson]

Hi,

The sheets are the same according to formulas and so, but the headline
are made as a calender from 1 jan to 31 dec. That means the headline
change for every sheet. 1 sheet=1week.

Is it still possible to use the idea of 2 sheets?

//Thoma

 

[Guest]

Yes, 2 sheets would be perfect for this purpose. This is how I would do it:

In the Raw Data sheet use column 1 as your index, say, "Week", so under that, it could be Week 1, Week 2, Week 3, ... and so on. Your next column may be a description of what the dates are within the week (Jan 1, 2004 - Jan 7, 2004, ... ) and after that just include a column for each field in your summary sheet (e.g. ID, Name, Cost, .... )

In your summary sheet, make it looks exactly like one of your 106 sheets. However, instead of having hard numbers in it, you just use "vlookup" and reference it on the cell that contains the week number (say, "Week 3") and use the raw data sheet as your lookup table. It will then pull the data that matches the week you specified.

That way, each line in the raw data sheet contains the data of each previously 106 sheets. The summary sheets uses the week number to lookup at the appropriate line from the raw data sheet. This will save you much time when you do analysis on the data later on.

 

[Jonsson]

OK,

I´ll try.........

Thanks for your responses!!

//Thoma