A
Alan
Hi All,
Environment = Excel 2007, Centrino Dual-core 2.3MHz processor, 2Gb
RAM, WinXP Pro.
I am trying to optimise a fairly large set of workbooks for a client,
some of which have multiple worksheets, and some of those worksheets
are fairly big (e.g. 2000 rows x 200 columns of calcs = 400,000 cells
per sheet, say 10 sheets, say 5 workbooks = 20m cells if my maths are
good and of course it varies across the books).
My question is what would be optimal in terms of calculation time.
To simplify, we'll narrow it down to one workbook with two sheets.
Sheet1 contains the 'source' data (lets assume 300 product names)
which do change.
Sheet2 contains multiple working areas where those product names are
referenced six times (assume a vertical layout with dates across the
top, and six 'sections' of calculations each of which is 300 rows deep
to give a total of just under 2000 rows).
Option 1: Linking each of those six sections in Sheet2 to the source
Sheet1 separately (minimises dependencies since there is only one
level of dependency, but means that I have 1800 links to another
sheet)
Option2: Linking the top section of Sheet2 to the source Sheet1, and
each of the other five sections of Sheet2 to the top section of Sheet2
(creates an additional level of dependency - now three deep (including
the source) rather than two deep (inc source), but means that I only
have 300 links to another sheet.
I understand that there are lots of other factors in optimising a set
of linked workbooks and calculation time (not least of which is that
we have already moved to Excel 2007 and a dual core machine), and we
will be pursuing those, but I have to make the changes one at a time
and test to be sure that it all still works as the client is very
dependent on this for now.
Therefore, please do offer any other suggestions on optimisation, but
I need to address this specific design question in the first instance,
so please offer your opinion on this one too!
It would also be interesting from a basic design perspective for any
future design / build assignments I may get!
I have set follow-ups to microsoft.public.excel as this seems a fairly
general thread.
Thanks in advance,
--
Alan.
The views expressed are my own, and not those of my employer or anyone
else associated with me.
My current valid email address is:
(e-mail address removed)
This is valid as is. It is not munged, or altered at all.
It will be valid for AT LEAST one month from the date of this post.
If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.
The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:
ewygchvboocno43vb674b6nq46tvb
Environment = Excel 2007, Centrino Dual-core 2.3MHz processor, 2Gb
RAM, WinXP Pro.
I am trying to optimise a fairly large set of workbooks for a client,
some of which have multiple worksheets, and some of those worksheets
are fairly big (e.g. 2000 rows x 200 columns of calcs = 400,000 cells
per sheet, say 10 sheets, say 5 workbooks = 20m cells if my maths are
good and of course it varies across the books).
My question is what would be optimal in terms of calculation time.
To simplify, we'll narrow it down to one workbook with two sheets.
Sheet1 contains the 'source' data (lets assume 300 product names)
which do change.
Sheet2 contains multiple working areas where those product names are
referenced six times (assume a vertical layout with dates across the
top, and six 'sections' of calculations each of which is 300 rows deep
to give a total of just under 2000 rows).
Option 1: Linking each of those six sections in Sheet2 to the source
Sheet1 separately (minimises dependencies since there is only one
level of dependency, but means that I have 1800 links to another
sheet)
Option2: Linking the top section of Sheet2 to the source Sheet1, and
each of the other five sections of Sheet2 to the top section of Sheet2
(creates an additional level of dependency - now three deep (including
the source) rather than two deep (inc source), but means that I only
have 300 links to another sheet.
I understand that there are lots of other factors in optimising a set
of linked workbooks and calculation time (not least of which is that
we have already moved to Excel 2007 and a dual core machine), and we
will be pursuing those, but I have to make the changes one at a time
and test to be sure that it all still works as the client is very
dependent on this for now.
Therefore, please do offer any other suggestions on optimisation, but
I need to address this specific design question in the first instance,
so please offer your opinion on this one too!
It would also be interesting from a basic design perspective for any
future design / build assignments I may get!
I have set follow-ups to microsoft.public.excel as this seems a fairly
general thread.
Thanks in advance,
--
Alan.
The views expressed are my own, and not those of my employer or anyone
else associated with me.
My current valid email address is:
(e-mail address removed)
This is valid as is. It is not munged, or altered at all.
It will be valid for AT LEAST one month from the date of this post.
If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.
The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:
ewygchvboocno43vb674b6nq46tvb