I wonder if someone could give me some advice please? This sounds like
a simple task but I'm having trouble approaching it. I'm not new to
programming, and I'm not new to Excel, but I am new to VBA, which
probably explains my indecision. I'll simplify the task:

I have a workbook with two worksheets in it. They contain a similar
list of entries, identified uniquely by a contract_id. In other words,
each row in Worksheet1 has a corresponding row in Worksheet2, linked by
a common contract_id. In Worksheet1, every working day over the next
year or so is represented by a column, where I can enter a figure
showing the number of 'man-days' worked on the contract entered in that
row.

Worksheet2 is something similar, except that here, instead of
individual days, I just have the year broken down into quarters: Q1,
Q2, Q3 and Q4. I want to have Excel update the Quarter cells by
counting the sum of the days in the corresponding contract row in the
other sheet.

If the contracts always remained in the same order in both sheets, I
could use a simple formula in Worksheet2 that went and found the
relevant Quarter value in Worksheet1. But the two ranges are often
sorted by different things: contract_id, customer name, value,
percentage, and so on. So they will sometimes not be in the same order
on the two worksheets.

So I want to be able to enter (manually) figures in Worksheet1
representing man-days and see them totalled into Quarters when I open
Worksheet2, even if the contracts are in a different order.

In programming terms, I want to point to a cell value (ie the
contract_id), tell Excel to go and find a match in a range on another
sheet, and then sum some cells in the same row of that second sheet
(presumably using an offset?).

The task seems pretty simple to be honest, but I'm just beating my head
against a wall here trying to make it work. Does anyone have some
advice please about the kind of approach I should take?

Thank you in advance

Will

If I am understanding correctly, you dont need to program
anything...just use the built in function SUMIF in excel.

Will,

You could use something like

=SUMPRODUCT((WorkSheet1!A3:A100=A2)*WorkSheet1!B3: IV100)

where your contract ID that you are interested in is entered into cell A2,
and there is 100 rows of data on Worksheet1.

That said, to get the Quarters, you would really get into badly complicated
formulas.

So my recommendation would be to learn how to use a database, to feed a
pivot table.

Enter your data in 3 columns like:

Contract Date Worker-Days

Then enter your values only in those 3 columns, way down the page.

Then you can use pivot tables to show summaries by contract, by date, by
week, by month, by quarter, etc, using the built-in functionality of Excel
pivot tables, without ever using a single formula.

HTH,
Bernie
MS Excel MVP

wrote in message
oups.com...
I wonder if someone could give me some advice please? This sounds like
a simple task but I'm having trouble approaching it. I'm not new to
programming, and I'm not new to Excel, but I am new to VBA, which
probably explains my indecision. I'll simplify the task:

I have a workbook with two worksheets in it. They contain a similar
list of entries, identified uniquely by a contract_id. In other words,
each row in Worksheet1 has a corresponding row in Worksheet2, linked by
a common contract_id. In Worksheet1, every working day over the next
year or so is represented by a column, where I can enter a figure
showing the number of 'man-days' worked on the contract entered in that
row.

Worksheet2 is something similar, except that here, instead of
individual days, I just have the year broken down into quarters: Q1,
Q2, Q3 and Q4. I want to have Excel update the Quarter cells by
counting the sum of the days in the corresponding contract row in the
other sheet.

If the contracts always remained in the same order in both sheets, I
could use a simple formula in Worksheet2 that went and found the
relevant Quarter value in Worksheet1. But the two ranges are often
sorted by different things: contract_id, customer name, value,
percentage, and so on. So they will sometimes not be in the same order
on the two worksheets.

So I want to be able to enter (manually) figures in Worksheet1
representing man-days and see them totalled into Quarters when I open
Worksheet2, even if the contracts are in a different order.

In programming terms, I want to point to a cell value (ie the
contract_id), tell Excel to go and find a match in a range on another
sheet, and then sum some cells in the same row of that second sheet
(presumably using an offset?).

The task seems pretty simple to be honest, but I'm just beating my head
against a wall here trying to make it work. Does anyone have some
advice please about the kind of approach I should take?

Thank you in advance

Will

Look at SUMPRODUCT()
If you have a column in sheet1 (say D) that also identifies the quarter you
could do somthing like this:
sheet1 = ContNum Sheet2=ContSum
ContNum format
A=ContNum B=Date C=Hrs D=Qtr

ContSum format
A=ContNum B=Qt1 C=Qt2 D=Qt3 E=Qt4

So: in cells B2 enter this formula and copy right and down
=SUMPRODUCT(-(ContNum!$A$2:$A$1000=$A2),-(ContNum!$D$2:$D$1000=B$1),ContNum!$C$2:$C$1000)

The Quarter totals in ContSum will be automatically calculated each time to
enter data in ContNum

Glen

wrote in message
oups.com...
I wonder if someone could give me some advice please? This sounds like
a simple task but I'm having trouble approaching it. I'm not new to
programming, and I'm not new to Excel, but I am new to VBA, which
probably explains my indecision. I'll simplify the task:

I have a workbook with two worksheets in it. They contain a similar
list of entries, identified uniquely by a contract_id. In other words,
each row in Worksheet1 has a corresponding row in Worksheet2, linked by
a common contract_id. In Worksheet1, every working day over the next
year or so is represented by a column, where I can enter a figure
showing the number of 'man-days' worked on the contract entered in that
row.

Worksheet2 is something similar, except that here, instead of
individual days, I just have the year broken down into quarters: Q1,
Q2, Q3 and Q4. I want to have Excel update the Quarter cells by
counting the sum of the days in the corresponding contract row in the
other sheet.

If the contracts always remained in the same order in both sheets, I
could use a simple formula in Worksheet2 that went and found the
relevant Quarter value in Worksheet1. But the two ranges are often
sorted by different things: contract_id, customer name, value,
percentage, and so on. So they will sometimes not be in the same order
on the two worksheets.

So I want to be able to enter (manually) figures in Worksheet1
representing man-days and see them totalled into Quarters when I open
Worksheet2, even if the contracts are in a different order.

In programming terms, I want to point to a cell value (ie the
contract_id), tell Excel to go and find a match in a range on another
sheet, and then sum some cells in the same row of that second sheet
(presumably using an offset?).

The task seems pretty simple to be honest, but I'm just beating my head
against a wall here trying to make it work. Does anyone have some
advice please about the kind of approach I should take?

Thank you in advance

Will

Chip - I've been trying SUMIF, but can't seem to get it to work. Have
also used DSUM without success. It may be that I just haven't got my
head around them.

Trouble is, I'm inheriting a spreadsheet that was 'designed' by someone
else and has become well established and liked by the users so I can't
change the structure too much.

But thanks for the suggestions so far, Guys. I won't be able to try out
these things for a day or so but will report back when I do.

Cheers

Will

Chip - I've been trying SUMIF, but can't seem to get it to work. Have
also used DSUM without success. It may be that I just haven't got my
head around them.

Trouble is, I'm inheriting a spreadsheet that was 'designed' by someone
else and has become well established and liked by the users so I can't
change the structure too much.

But thanks for the suggestions so far, Guys. I won't be able to try out
these things for a day or so but will report back when I do.

Cheers

Will