W
willcarter
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
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