Hi, so I had a crazy idea to try to link availability and price sheets at work, but am having trouble with some of how to pull it off. The availability sheet is a matrix with product type info down the first few columns. Week of the year is across the top. a "1" is marked in the cell that lines up with weeks and product types if they are available. Pricing sheet is laid out in the same way, though product types and weeks are clumped to be more visually appealing (the sheets do not line up perfectly). Prices are in place of the "1" from the other sheet and are in every slot - not just those that are available.
I would like to highlight the price cells that have available products so it is easy to see without needing to go back and forth between 2 sheets. I think I need to use conditional formatting with some type of "if" statement, or probably multiple functions.
key:
Sheet 2 (Availability) AD:AJ and AW:AZ make up one particular time chunk (would be changed for other availability timeslots). Column C is unit type
Sheet 1 (Price) Column B is unit type, Column F is weeks (which again would change when copying this function)
TL/DR: Actual Question: In words what I want to do (i think) is:
IF there is a "1" in sheet 2 columns AD:AJ OR AW:AZ and the exact cell value in sheet 2 column C for that row with the "1" matches ANY of the text in Sheet 1 Column B, Then highlight the cell in the same row in Sheet 1 column F
Thanks
Bookmarks