jamieleeuk
Board Regular
- Joined
- Feb 9, 2009
- Messages
- 99
Hi,
I've not posted for a long time, but now I am need help again and my first port of call was Mr Excel.
I have two tables on the same sheet
<tbody>
</tbody>
I have a dropdown list in cell H1 which lists the unique Batch numbers the left hand table and in cell H2, I want the following to happen:
Say for instance I chose AA0001 in the drop down box, in cell H2, I would like excel to add up all the 'weight' values for batch number AA0001 in the left hand table and then deduct the sum of values with the corresponding Batch numbers in the right hand table.
So, if I chose AA0001 from the drop down list, H2 would give me the answer of 4,000 (being the 10,000 we received in, less the 3,000 plus 2,000 plus 1,000 that we have sent out).
For the life of me I cannot get the hang over Index Match or Sumproduct and I'm sure you guys will be able to tell me in the blink of an eye!
Thanks for any assistance,
Jamie
I've not posted for a long time, but now I am need help again and my first port of call was Mr Excel.
I have two tables on the same sheet
A | B | C | D | E | F | G | H | I | J |
batch # | weight in | Batch num | weight out | ||||||
AA0001 | 10,000 | AA0001 | 3,000 | ||||||
AA0002 | 7,000 | AA0001 | 2,000 | ||||||
AA0003 | 6,000 | AA0002 | 4,000 | ||||||
AA0004 | 12,000 | AA0003 | 2,000 | ||||||
AA0003 | 3,000 | ||||||||
AA0003 | 1,000 | ||||||||
AA0001 | 1,000 | ||||||||
AA0004 | 7,000 |
<tbody>
</tbody>
I have a dropdown list in cell H1 which lists the unique Batch numbers the left hand table and in cell H2, I want the following to happen:
Say for instance I chose AA0001 in the drop down box, in cell H2, I would like excel to add up all the 'weight' values for batch number AA0001 in the left hand table and then deduct the sum of values with the corresponding Batch numbers in the right hand table.
So, if I chose AA0001 from the drop down list, H2 would give me the answer of 4,000 (being the 10,000 we received in, less the 3,000 plus 2,000 plus 1,000 that we have sent out).
For the life of me I cannot get the hang over Index Match or Sumproduct and I'm sure you guys will be able to tell me in the blink of an eye!
Thanks for any assistance,
Jamie
Last edited: