Formula to/from other sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Khalil Handal - 12 Dec 2005 14:48 GMT

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

Reply to this Message

Don Guillett - 12 Dec 2005 15:05 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Hi,
>
[quoted text clipped - 20 lines]
>
> Thank in advance

Reply to this Message

Dave Peterson - 12 Dec 2005 15:10 GMT

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

> Put them all in the same cell on each sheet and then use
> =sum(firstsheetname!:lastsheetname!,a1)
[quoted text clipped - 27 lines]
> >
> > Thank in advance

Signature

Dave Peterson

Reply to this Message

Don Guillett - 12 Dec 2005 16:18 GMT

Thanks for the correction, Dave

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> Just a typo...
> =sum(firstsheetname:lastsheetname!a1)
[quoted text clipped - 32 lines]
>> >
>> > Thank in advance

Reply to this Message

Khalil Handal - 12 Dec 2005 17:03 GMT

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

> Put them all in the same cell on each sheet and then use
> =sum(firstsheetname!:lastsheetname!,a1)
[quoted text clipped - 23 lines]
>>
>> Thank in advance

Reply to this Message

Don Guillett - 12 Dec 2005 19:14 GMT

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.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> First of thanks a lot.
> This means that I have to make the calculation of :
[quoted text clipped - 37 lines]
>>>
>>> Thank in advance

Reply to this Message