Formula to/from other sheet

[Khalil Handal]

Hi,

I have a sheet named "reports". In the cell C9 I need to have a value taken
from 12 sheets named "June 2005", "July 2005" .. Until I reach sheet "May
2006"

The value I need has to be calculated from 3 different cells:



This formula didn't work well since cells C177 & D177 were taken from the
active sheet and not from sheet "June 2005".

='June 2005'!$D$1*C177+D177





The formula that worked well is:

='June 2005'!C170*'June 2005'!$D$1+'June 2005'!D170



My question is:

Is it necessary to REPEAT the sheet's name (June 2005) three time?

Is there a way not to repeat the sheet's name 3 time?



Thank in advance

 

[Don Guillett]

Put them all in the same cell on each sheet and then use
=sum(firstsheetname!:lastsheetname!,a1)

 

[Dave Peterson]

Just a typo...
=sum(firstsheetname:lastsheetname!a1)

 

[Don Guillett]

Thanks for the correction, Dave

 

[Khalil Handal]

First of thanks a lot.
This means that I have to make the calculation of :
=C170*$D$1+D170
in one cell say C180
and do this for all the other sheets.

I thoough it can be done automaticaly without having the answer on the same
sheet.
Note:
I have othe sheets n the work book that are not included in the equation

Khalil

 

[Don Guillett]

my suggestion assumes that the sheets you want to sum are together.
1,2,3,4,5,6,7,8,9,10,11,12,13,14
you could use=sum(sheet2:sheet13!a1)
if some other configuration, I guess you will have to do it the other way.