One method which may work for you. add a work sheet (Helper sheet)
If your main sheet has less than 300 rows and is called Input_WC! and starts
in column R
in the helper sheet A1
=offset(Input_WC!$A$1,mod(Row(),300)-1,floor((Row()-1)/300)+20)
in B1
=offset(Input_WC!$A$1,mod(Row(),300)-1,floor((Row()-1)/300)+18)
in C1
=offset(Input_WC!$A$1,mod(Row(),300)-1,floor((Row()-1)/300)+21)
in D1
=offset(Input_WC!$A$1,mod(Row(),300)-1,floor((Row()-1)/300)+23)
Note don't be surprized if you have to play with the equations a bit to get
them to reference the right columns in your master File
It might also be worth your while to manipulate your master sheet just a
touch so that the column references can be regular.
since you have 58 sets
copy A1:D1to A15600:D15600
(Simplest way to do this is to select and copy A1:D1
go to the little box to the left of the formula bar and enter A1:D1560
enter to select the cells and then paste.
Now do your sumproduct as
=SUMPRODUCT(--(Helper!A1:A15600=$B$1),--(Helper!B1:B15600=$A14),--(Helper!C1:C15600=$B$2),Helper!D1:D15600)
"yesac142" wrote:
Thanks, I'm getting closer I think. However, I'm not sure that I've
been clear enough (more detail below). The examples are for 2 sets of
columns... I actually have 58 sets to conditionally sum. I suppose
that I could write a huge formula and include all the sets. I think
that SUMPRODUCT will handle 30 at a time.
The following explains 1 set of columns, which is repeated 58 times.
The sheet will need to handle at least 250 rows (not related to no. of
items).
ITEM = (250 items) the pay item number, revenue and cost associated
QUANTITY = (number) the amount of the ITEM to be billed
INVOICE = (number) period (month) in which to bill the QUANTITY
CONTRACTOR = (5 different ones) company responsible for the work
I'm no good with VB/macros, but all ideas are appreciated. Thank you
for your help and patience.
--
yesac142
------------------------------------------------------------------------
yesac142's Profile: http://www.excelforum.com/member.php...o&userid=24391
View this thread: http://www.excelforum.com/showthread...hreadid=379938