Change cell color based on data from another workbook

Question

I have two spreadsheets, A and B, residing in different workbooks. I may not make changes to Spreadsheet A. Both contain a list of products. Spreadsheet A has dates corresponding to product as "start" and "stop" columns delimiting a range of dates. Spreadsheet B has the same data but with columns representing individual days:

Spreadsheet A looks like this:

Product Name | Product Code | Start date | Stop Date

Spreadsheet B:

Product Name | Product Code| 1.01|1.02|1.03 etc.

In Spreadsheet B by row I want cells automatically filled black for every day (column) in the timeframe specified in Spreadsheet A. Spreadsheet A is used by entire company and I can't make changes to it. Basically how do I change the data format between spreadsheets from "Start in one cell, Stop in another" to "From Cell 1 to Cell x"? EDIT: I created a Spreadsheet C in Workbook B that copies both dates and calculates amount of cells that need to be blacked out.

Answer 1

If your Product Names and Product Codes are not unique pairs then I'd suggest starting by concatenating these (eg ColumnC in both spreadsheets) - may well not be necessary. Then, to be able to apply conditional formatting, name ColumnsC:E in SheetA as a range (say RangeCDE). Finally a formula that returns TRUE where required can be applied in conditional formatting in D2 (say =AND(VLOOKUP($C2,RangeCDE,2,FALSE)<=D$1,VLOOKUP($C2,RangeCDE,3,FALSE)>=D$1)):

There is no need to apply the formula in the cells, though that is as shown in the example. (May help if to adjust the formula to exclude either of the limits presently inclusive.)

The Applies to range would need to be =$D$2:{and as far as required}.

For test data I used:

Note that all the dates are 'real' dates though formatted differently in SheetB.