Vlookup/Match on dates mid week 1

[Topcat04]

Hi There

Im hoping this is possible.

We have a file that is produced each week that contains shipment details.

Column A uses the sku number to identify the product.
Column D has the qty being shipped
Column H has the departure date
(The other columns contain additional data that is not required for this report)

We have another spreadsheet that contains a list of all skus in column A (row 6 through to row 70)

Column D contains the first Monday of the year (04/01/2010) and this goes from Cell D3 through to Cell BD3 in increments of 7 days)

Under each sku (row 6 to row 70) and each relevent date I need to be able to place the Qty into the cell. An additional challenge is that there are shipping dates that do not fall on a Monday that week but would need to show under the date with the Monday starting date of that week.

For example.
Sheet 1
Cell A36 contains product1
Cell D36 contains 5000
Cell H36 contains 06/01/2010

so on sheet 2
Product 1 is in cell A43
Cell D3 date is 04/01/2010
So in Cell D43 I would like the sheet to show 5,000 (the qty from Sheet1) as the 06/01/2010 falls in that week.

I hope I have explained this well and look forward to your help in solving how to do this. I am guessing it is via a form of vlookup/match formula?

Appreciate your help,

 

[SkipVought]

Hi,

I'd use the SUMPRODUCT function.

Let's say that your sheet1 range for dates is a Named Range ShipDte and the product is SKU and qty is Qty. On sheet2...
[tt]
=SUMPRODUCT((SKU=$A$43)*(ShipDte>=D2)*(ShipDte<D3)*(QTY))
[/tt]
where D2 is week n and D3 is week n+1.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Skip

Thats awesome! thank you so much for your quick response.

Could it be tweaked so that if column G (shipment method) on sheet 1 contained Air, that the qty on sheet 2 in the appropriate cell was displayed in Red?

That would be the icing on the cake!

Thanks again for your quick response and great solution.

- TC

 

[SkipVought]

Give the column G data a Named Range name like ShipMethod

Then use Conditional Formatting, with...
[tt]
Formula Is: =SUMPRODUCT((SKU=$A$43)*(ShipMethod="Air")*(ShipDte>=D2)*(ShipDte<D3))>1
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Skip

Sorry that one has got me - I either havent understood properly how to apply the conditional formula, or I have not been able to get that one to work.

Column G on sheet 1 has a named range as you suggest.

then I applied the conditional formatting to the cell on Sheet 2.

Is this correct? if so, I could not get the conditional format to work..

Thanks for your help,
- TC

 

[SkipVought]

Please post a sample of data from sheet1 (copy 'n' paste).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

So sorry!
[tt]
Formula Is: =SUMPRODUCT((SKU=$A$43)*(ShipMethod="Air")*(ShipDte>=D2)*(ShipDte<D3))>[red]0[/red]
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Thnx Skip

Thats resolved it!

All the best,
TC

 

[Topcat04]

Skip

Sorry, I tried to edit the formula to apply an additional format to the cell, that if PO_NUM was USA to make it go Italic.

However it again does not seem to be responding.

I took the formula you provided above - created a new formula pasted the formula into Formula IS and changed ShipMethod="Air" to PO_NUM="USA"

This means I have two conditional formattings but only the Shipmethod is working.

How did I screw up?

Thanks,
TC

 

[SkipVought]

You have the carefully think thru the LOGIC that you want to apply.

You have THREE conditions. It can ONLY satisfy ONE of the three conditions. Any condition that is satisfied, gets ONE format as defined by you.

So if you have a condition that BOTH ShipMethod="Air" and PO_NUM="USA", THAT condition must be the FIRST one, AND it gets whatever format.
[tt]
Condition1: BOTH
=SUMPRODUCT((SKU=$A$43)*(ShipMethod="Air")*(PO_NUM="USA")*(ShipDte>=D2)*(ShipDte<D3))>0
Apply format1 and DONE!

Condition2: ONLY "Air"
=SUMPRODUCT((SKU=$A$43)*(ShipMethod="Air")*(ShipDte>=D2)*(ShipDte<D3))>0
Apply format2 and DONE!
[/tt]



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Topcat04]

Skip

Thats brilliant...thank you for taking the time to explain in detail!

Have a great day,
TC

 

[SkipVought]

I should really have stated...

You have THREE conditions. It can ONLY satisfy ONE or NONE of the three conditions. Any condition that is satisfied, gets ONE format as defined by you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}