Help with alternative to very repetitive sum.if formula"?

[Littleme]

Hello again!

I have a workbook that works pretty well, but I cant help but think that when it grows to cover an a project plan of an entire year, (is only 3 months now) it will become really slow.

I'm struggling with the formula on the second sheet, there has got to be a better solution!
What I have is:
=OM(ELLER(SUMMA.OM(Tabell_SammanFrånvaro[[Namn]:[Namn]];$A2;Tabell_SammanFrånvaro[2015-06-01])=-1;1=SUMMA.OM(Tabell_SammanSyssel[[Namn]:[Namn]];$A2;Tabell_SammanSyssel[2015-06-01]));0;SUMMA(SUMMA.OM(Tabell_SammanSyssel[[Namn]:[Namn]];$A2;Tabell_SammanSyssel[2015-06-01]);'Planera här'!K22))

Feels VERY repetitive.

If anyone has the time to look and the file I would be very grateful. All feed back and thoughts are welcome!

 

[NARAYANK991]

Hi ,

This inviting feedback after a workbook has been in use for 3 months is probably somewhat too late.

Feedback is more useful if it is asked for while or even before an application is developed.

All your tables are done , all your derived formulae are done , and any change at this stage can probably mean undoing everything that you have done till now !

At this stage you can either make cosmetic changes or redo the whole thing from scratch.

Doing the latter will mean writing down the functional specifications for the complete application , right from what kind of data will be entered , how it will be entered , how it will be processed and finally what kind of outputs will be required.

The maximum time of execution is taken up by the formulae on the two Crosstab sheets ; if you can think of redoing this using VBA , your overall recalculation times will improve. At present there are hardly a dozen names on these 2 sheets ; I do not know if you will put in more names , in which case execution times will only go up.

Narayan

 

[Littleme]

Hi!
Thank you for taking the time to respond. But to make things clear the workbook is NOT in use. That is why I'm asking for feedback.

I like the idea of using vba for crosstab- sheets. Any idea how that might look?

 

[NARAYANK991]

Hi ,

We will need to know what the Crosstab sheets will contain ; how many names , how many dates ,... Only if all possible information is made available , can fool-proof code be written.

Narayan

 

[Littleme]

Hi again!
If you feel you have the time to help I would be very grateful for input.
Dates across will be 365 days from whatever start date the user chooses on first worksheet.

The number of users may vary but shouldnt be more than 15 names.

I sm very happy to set up helper sheets if necessary.

The list that the crosstab will "search" is can be pretty massive 36500 rows. Dont know if thats stupid? If it is then say so and I'll rethink the whole idea.

Does this help you?