A user has a file consisting of 12 period sheets and 1 summary sheet. She
has a very long formula on the summary: D5*$D$2 + F5*$F$2 and so on through
column AJ. At the end of the formula she needs to add more numbers that come
from the individual period sheets: ='Per 1'!AG5*'Per 1'!AG2 and so on through
Per 12. She has a "helper" column set up that adds ='Per 1'!AG5*'Per 1'!AG2
+ 'Per 2'!AG5*'Per 2'!AG2 through Per 12. She adds the result of the helper
column to the end of the first formula. Is there a way to lose the helper
column and add a shorter 3-dimensional formula there? I'm not very good with
arrays, and I suspect we're being stupid here. TIA! Excel 2003.

We can have another row to hold the product of D5*$D$2,..... and add up the
sum for that row.



"cottage6" wrote:

A user has a file consisting of 12 period sheets and 1 summary sheet. She
has a very long formula on the summary: D5*$D$2 + F5*$F$2 and so on through
column AJ. At the end of the formula she needs to add more numbers that come
from the individual period sheets: ='Per 1'!AG5*'Per 1'!AG2 and so on through
Per 12. She has a "helper" column set up that adds ='Per 1'!AG5*'Per 1'!AG2
+ 'Per 2'!AG5*'Per 2'!AG2 through Per 12. She adds the result of the helper
column to the end of the first formula. Is there a way to lose the helper
column and add a shorter 3-dimensional formula there? I'm not very good with
arrays, and I suspect we're being stupid here. TIA! Excel 2003.

cottage6;281585 Wrote:
A user has a file consisting of 12 period sheets and 1 summary sheet.
She
has a very long formula on the summary: D5*$D$2 + F5*$F$2 and so on
through
column AJ. At the end of the formula she needs to add more numbers that
come
from the individual period sheets: ='Per 1'!AG5*'Per 1'!AG2 and so on
through
Per 12. She has a "helper" column set up that adds ='Per 1'!AG5*'Per
1'!AG2
+ 'Per 2'!AG5*'Per 2'!AG2 through Per 12. She adds the result of the
helper
column to the end of the first formula. Is there a way to lose the
helper
column and add a shorter 3-dimensional formula there? I'm not very good
with
arrays, and I suspect we're being stupid here. TIA! Excel 2003.A good start would be to post the formula and/or sample workbook!, it is
almost impossible to give advice on the formula as you have explained
it.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=78657

Thanks to both of you; I think I've figured something else out.

"Simon Lloyd" wrote:


cottage6;281585 Wrote:
A user has a file consisting of 12 period sheets and 1 summary sheet.
She
has a very long formula on the summary: D5*$D$2 + F5*$F$2 and so on
through
column AJ. At the end of the formula she needs to add more numbers that
come
from the individual period sheets: ='Per 1'!AG5*'Per 1'!AG2 and so on
through
Per 12. She has a "helper" column set up that adds ='Per 1'!AG5*'Per
1'!AG2
+ 'Per 2'!AG5*'Per 2'!AG2 through Per 12. She adds the result of the
helper
column to the end of the first formula. Is there a way to lose the
helper
column and add a shorter 3-dimensional formula there? I'm not very good
with
arrays, and I suspect we're being stupid here. TIA! Excel 2003.A good start would be to post the formula and/or sample workbook!, it is
almost impossible to give advice on the formula as you have explained
it.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=78657