Date comparisons and alerts

[Kirbylink]

I work in a hotel in Room Service. What I need to do is create a spreadsheet that will track product in a room by expiration date. For example, a bottle of coke will expire on July 21, I'd like the spreadsheet to alert me that the bottle will expire in 14 days. This way I can get that bottle out to avoid any expired items in the room. It doesn't have to scream at me, it could simply change the information red to inform me. I know how to have the spreadsheet update the date daily but I don't know how to have another cell compare itself to the daily date. If anybody has any suggestions, I'll be glad to hear them.

 

[kaoscore]

You can doit this with conditional format in the cells that contain the dates. For example in the conditional you add a formulate, like this

= cell_whit_date_to_evaluate < TODAY() - 15

 

[Kirbylink]

That's the best news I've heard all day. Thank you very much, it now makes the task all the much easier to handle, thank you.

 

[Kirbylink]

They say that for every answer there are ten more questions...I have another bigger one. Because of the last answer I was given I was able to create a HUGE spreadsheet with all the values I needed. Unfortunately because the sheet is so big, searching it would be a very big nuisance after the novelty of it wore off. Now what I'm looking for is a way to grab information from one part of the spreadsheet and display that information in another part of the spreadsheet such as the main page. I know I can use the search feature of excel, but I don't think the workers will be able to decipher it quickly enough.

 

[PaddyD]

give us more if on the data you've got in your sheet & what you want transferred to where...

 

[Kirbylink]

What I have so far is the header taking up cells A1:G1 merged. Cell A2 contains the date that will be used for comparison (daily). Cell A5 contains the building name. B5 contains the cell that will be used to call information from other parts of the sheet. C5:C14 and F5:F14 contain the product names while D5:D14 and G5:G14 contain the arguments that will advise me when a product must be removed. The same is repeated two rows down for the following room. My intention is to be able to type in a room number such as 101 in cell B5 and have cells C5:G14 display the contents of cells C17:G26. Any help is always appreciated.

DATE


BUILDING 1 SEARCH PRODUCT ARGUMENT PRODUCT
ARGUMENT
PRODUCT ARGUMENT PRODUCT ARGUMENT
PRODUCT ARGUMENT PRODUCT ARGUMENT
PRODUCT ARGUMENT PRODUCT ARGUMENT
PRODUCT ARGUMENT PRODUCT ARGUMENT
PRODUCT ARGUMENT PRODUCT ARGUMENT
PRODUCT ARGUMENT PRODUCT ARGUMENT
PRODUCT ARGUMENT PRODUCT ARGUMENT
PRODUCT ARGUMENT PRODUCT ARGUMENT
PRODUCT ARGUMENT PRODUCT ARGUMENT


ROOM 1 PRODUCT 1 ARGUMENT PRODUCT 11 ARGUMENT
PRODUCT 2 ARGUMENT PRODUCT 12 ARGUMENT
PRODUCT 3 ARGUMENT PRODUCT 13 ARGUMENT
PRODUCT 4 ARGUMENT PRODUCT 14 ARGUMENT
PRODUCT 5 ARGUMENT PRODUCT 15 ARGUMENT
PRODUCT 6 ARGUMENT PRODUCT 16 ARGUMENT
PRODUCT 7 ARGUMENT PRODUCT 17 ARGUMENT
PRODUCT 8 ARGUMENT PRODUCT 18 ARGUMENT
PRODUCT 9 ARGUMENT PRODUCT 19 ARGUMENT
PRODUCT 10 ARGUMENT PRODUCT 20 ARGUMENT

ROOM 2 PRODUCT 1 ARGUMENT PRODUCT 11 ARGUMENT
PRODUCT 2 ARGUMENT PRODUCT 12 ARGUMENT
PRODUCT 3 ARGUMENT PRODUCT 13 ARGUMENT
PRODUCT 4 ARGUMENT PRODUCT 14 ARGUMENT
PRODUCT 5 ARGUMENT PRODUCT 15 ARGUMENT
PRODUCT 6 ARGUMENT PRODUCT 16 ARGUMENT
PRODUCT 7 ARGUMENT PRODUCT 17 ARGUMENT
PRODUCT 8 ARGUMENT PRODUCT 18 ARGUMENT
PRODUCT 9 ARGUMENT PRODUCT 19 ARGUMENT
PRODUCT 10 ARGUMENT PRODUCT 20 ARGUMENT

ROOM 3 PRODUCT 1 ARGUMENT PRODUCT 11 ARGUMENT
PRODUCT 2 ARGUMENT PRODUCT 12 ARGUMENT
PRODUCT 3 ARGUMENT PRODUCT 13 ARGUMENT
PRODUCT 4 ARGUMENT PRODUCT 14 ARGUMENT
PRODUCT 5 ARGUMENT PRODUCT 15 ARGUMENT
PRODUCT 6 ARGUMENT PRODUCT 16 ARGUMENT
PRODUCT 7 ARGUMENT PRODUCT 17 ARGUMENT
PRODUCT 8 ARGUMENT PRODUCT 18 ARGUMENT
PRODUCT 9 ARGUMENT PRODUCT 19 ARGUMENT