Excel Formula Poser

[Peter J Elliott]

I have created a worksheet to provide details of resources. Within the
worksheet are separate sheets for each month this financial year (i.e.
Feb 2007 - March 2008)

Along the top I have three separate rows with differing rates for
different clients and below, I have a number of sections for those
different clients which list different projects.

Within each of these sections are formulae in the form:

=(C6*C15)+(D6*D15)+(E6*E15)+(F6*F15)+(G6*G15)+(H6*H15)+(I6*I15)+(J6*J15)
+(K6*K15)+(L6*L15) or

=(C5*C26)+(D5*D26)+(E5*E26)+(F5*F26)+(G5*G26)+(H5*H26)+(I5*I26)+(J5*J26)
+(K5*K26)+(L5*L26) for instance

where rows 6 and 5 contain the rates (i.e. contstants for each client)
and rows 15 and 26 contains the number of hours allocated to the
project.

Row 6 remains constant for each client, but there may be in excess of
50no projects listed for each (each on a separate row) to which I add
weekly. It is a pain adding such a long formula each time and ensuring
that the constants remain, well, constant. Using the Excel copy function
adds increments of 1 to the row constant each time so does not work.

Can anyone suggest a more efficient way of doing the same job please?

Many thanks in anticipation.

Regards

Peter

 

[Bernard Liengme]

This
=(C6*C15)+(D6*D15)+(E6*E15)+(F6*F15)+(G6*G15)+(H6*H15)+(I6*I15)+(J6*J15)+(K6*K15)+(L6*L15)
can be coded as
=SUMPRODUCT(C6:L6,C15,l15)
best wishes from a Manchester City supporter
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

I have created a worksheet to provide details of resources. Within the worksheet are separate sheets for each month this financial year (i.e. Feb 2007 - March 2008)

Along the top I have three separate rows with differing rates for different clients and below, I have a number of sections for those different clients which list different projects.

Within each of these sections are formulae in the form:

=(C6*C15)+(D6*D15)+(E6*E15)+(F6*F15)+(G6*G15)+(H6*H15)+(I6*I15)+(J6*J15)+(K6*K15)+(L6*L15) or

=(C5*C26)+(D5*D26)+(E5*E26)+(F5*F26)+(G5*G26)+(H5*H26)+(I5*I26)+(J5*J26)+(K5*K26)+(L5*L26) for instance

where rows 6 and 5 contain the rates (i.e. contstants for each client) and rows 15 and 26 contains the number of hours allocated to the project.

Row 6 remains constant for each client, but there may be in excess of 50no projects listed for each (each on a separate row) to which I add weekly. It is a pain adding such a long formula each time and ensuring that the constants remain, well, constant. Using the Excel copy function adds increments of 1 to the row constant each time so does not work.

Can anyone suggest a more efficient way of doing the same job please?

Many thanks in anticipation.

Regards

Peter

 

[Bernard Liengme]

Oops!
=SUMPRODUCT(C6:L6,C15:L15)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

This
=(C6*C15)+(D6*D15)+(E6*E15)+(F6*F15)+(G6*G15)+(H6*H15)+(I6*I15)+(J6*J15)+(K6*K15)+(L6*L15)
can be coded as
=SUMPRODUCT(C6:L6,C15,l15)
best wishes from a Manchester City supporter
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

I have created a worksheet to provide details of resources. Within the worksheet are separate sheets for each month this financial year (i.e. Feb 2007 - March 2008)

Along the top I have three separate rows with differing rates for different clients and below, I have a number of sections for those different clients which list different projects.

Within each of these sections are formulae in the form:

=(C6*C15)+(D6*D15)+(E6*E15)+(F6*F15)+(G6*G15)+(H6*H15)+(I6*I15)+(J6*J15)+(K6*K15)+(L6*L15) or

=(C5*C26)+(D5*D26)+(E5*E26)+(F5*F26)+(G5*G26)+(H5*H26)+(I5*I26)+(J5*J26)+(K5*K26)+(L5*L26) for instance

where rows 6 and 5 contain the rates (i.e. contstants for each client) and rows 15 and 26 contains the number of hours allocated to the project.

Row 6 remains constant for each client, but there may be in excess of 50no projects listed for each (each on a separate row) to which I add weekly. It is a pain adding such a long formula each time and ensuring that the constants remain, well, constant. Using the Excel copy function adds increments of 1 to the row constant each time so does not work.

Can anyone suggest a more efficient way of doing the same job please?

Many thanks in anticipation.

Regards

Peter

 

[Peter J Elliott]

Thanks Bernard, much appreciated.

Regards

Peter

_____

From: Bernard Liengme [mailto:[email protected]]
Posted At: 09 February 2007 18:31
Posted To: microsoft.public.excel.newusers
Conversation: Excel Formula Poser
Subject: Re: Excel Formula Poser


Oops!
=SUMPRODUCT(C6:L6,C15:L15)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


This
=(C6*C15)+(D6*D15)+(E6*E15)+(F6*F15)+(G6*G15)+(H6*H15)+(I6*I15)+(J6*J15)
+(K6*K15)+(L6*L15)
can be coded as
=SUMPRODUCT(C6:L6,C15,l15)
best wishes from a Manchester City supporter
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


I have created a worksheet to provide details of resources. Within the
worksheet are separate sheets for each month this financial year (i.e.
Feb 2007 - March 2008)

Along the top I have three separate rows with differing rates for
different clients and below, I have a number of sections for those
different clients which list different projects.

Within each of these sections are formulae in the form:

=(C6*C15)+(D6*D15)+(E6*E15)+(F6*F15)+(G6*G15)+(H6*H15)+(I6*I15)+(J6*J15)
+(K6*K15)+(L6*L15) or

=(C5*C26)+(D5*D26)+(E5*E26)+(F5*F26)+(G5*G26)+(H5*H26)+(I5*I26)+(J5*J26)
+(K5*K26)+(L5*L26) for instance

where rows 6 and 5 contain the rates (i.e. contstants for each client)
and rows 15 and 26 contains the number of hours allocated to the
project.

Row 6 remains constant for each client, but there may be in excess of
50no projects listed for each (each on a separate row) to which I add
weekly. It is a pain adding such a long formula each time and ensuring
that the constants remain, well, constant. Using the Excel copy function
adds increments of 1 to the row constant each time so does not work.

Can anyone suggest a more efficient way of doing the same job please?

Many thanks in anticipation.

Regards

Peter