Alternative to an array formula.

Hello,

I recently posted a question in relation to the slow loading of a workbook. After trying many fixes mentioned on this site and the .www I have accepted that the slow opening maybe attributed to the large number of array formulae in my workbook. My workbook for an athletics club has 400 worksheets with each containing between 10 to 17 columns of 25 rows cont. formula. So in effect between 250 to 425 cells in each sheet containing one of the two array formulae. In addition to this I have the same number of cells in other columns containing a non array formula that looks up 20 worksheets when calculating.

My question is, is it the array formulae or the non array formulae that is slowing down the opening of the workbook and is there an alternative.

Currently workbook takes 2m55secs to open on HP laptop Intel(R)Core2Duo, 2.20 GHz 1.99G RAM. It takes 44min on an older laptop.

Once open the program works well and there is no lag in calc time.

Not all 400 worksheets are used. A worksheet is populated and used when a name is entered into a cell on a Registration worksheet. Realistically only 100of the worksheets maybe used but the additional sheets are there allowing for growth of the club.

Formula (In Col D) for better time PB (Personal Best) and =PB, checking data in col C.

Please Login or Register  to view this content.

Formula (In Col S) for better distance PB and =PB, checking data in Col R.

Please Login or Register  to view this content.

Formulae (In Col E & T) to check 20 sheets to determine if time/distance is best of all athletes.

Please Login or Register  to view this content.

Thanks
RunHard

Shg,

I have toyed with this idea that you have previously mentioned (slow workbook). The only issue being is that the data entry has to be very basic as it is volunteer parents that input the data. With the way I have it set up at present the parent clicks on the childs name and a hyperlink takes them to the childs data entry sheet. It doesn't matter what they do with the mouse they cannot leave this sheet until they hit the home icon. Having all the children on the one sheet (up to 400) could cause a panic with the parents if they strayed from the relevant childs data sheet. I hope that makes sense.


RunHard

Shg,

Still very new at this and what I have learnt so far has come from this site. If I am reading your post correctly you are saying to incorporate the 400 sheets into 1 worksheet and then insert a button to bring up the relevant childs sheet from this one worksheet. How do I lock that sheet down via the button so your data entry is limited to just that childs sheet when the child is selected. I might be missing your point.

RunHard

Will consolidating all of my worksheets into one worksheet solve my slow loading problem. It is a lot of work to do and I would hate to get to the end and find that it makes no difference. Size of the workbook will still be @ 20MB.


RunHard

Shg or anyone else,

Is it the array formulas in general that is possibly causing the problem or is it the array formulas over the whole 400 workheets. I guess my question is will it make any difference if I consolidate all the worksheets into one but still retain the array formulas.

Thanks

RunHard