jimbojones
08-07-2007, 06:25 AM
I don't really know how to summarise this problem, so I'm just going to try and explain it in as much detail as possible so somebody might be able to help me.
I have a spreadsheet with a number of sheets, the final sheet being "Summary" which gathers info from other sheets.
The sheet I need to get information has, amongst others, the following information:
A: PRODUCT CODE | B: DESPATCH DATE | C: FLAG
001...........................01/01/06..............0
001...........................05/01/06..............0
001...........................10/01/06..............1
002...........................01/02/06..............0
002...........................05/02/06..............0
002...........................10/02/06..............1
So for each product code there is numerous lines, one for each despatch date. The flag indicates the line for each product which is the LATEST dispatch date for that product. (Calculated using "today - despatch date"). This is just test data, there can be any number of rows per product code, not always three.
What I need is for the summary page, which only has ONE row per product code, to take the despatch date for each product code that is flagged as the latest despatch date.
So, I was thinking something that would search all the rows and say, "this cell = B[row number] IF A[row number] = 001 and C[row number] = 1". But I know that isn't the way to do it and I'm not sure how to do it.
Any ideas?
I have a spreadsheet with a number of sheets, the final sheet being "Summary" which gathers info from other sheets.
The sheet I need to get information has, amongst others, the following information:
A: PRODUCT CODE | B: DESPATCH DATE | C: FLAG
001...........................01/01/06..............0
001...........................05/01/06..............0
001...........................10/01/06..............1
002...........................01/02/06..............0
002...........................05/02/06..............0
002...........................10/02/06..............1
So for each product code there is numerous lines, one for each despatch date. The flag indicates the line for each product which is the LATEST dispatch date for that product. (Calculated using "today - despatch date"). This is just test data, there can be any number of rows per product code, not always three.
What I need is for the summary page, which only has ONE row per product code, to take the despatch date for each product code that is flagged as the latest despatch date.
So, I was thinking something that would search all the rows and say, "this cell = B[row number] IF A[row number] = 001 and C[row number] = 1". But I know that isn't the way to do it and I'm not sure how to do it.
Any ideas?