S
S
Stanislav Maslakov2018-11-21 13:01:38
Google Sheets
Stanislav Maslakov, 2018-11-21 13:01:38

Need to color google spreadsheet cells depending on date range in visiting calendar?

The essence of the task is this. To add on one sheet the room number in the hotel, the dates of arrival and departure of guests and f / and guests. And, on another page, so that the cells are filled in accordance with the entered data on the first page automatically.
Example:
booking sheet
cell A2 - enter the room number (2)
cell B2 - arrival date (05/15/2019)
cell C2 - departure date (05/25/2019)
cell B2 - number of days (10)
cell E2 - last name and first name
calendar sheet
is required so that the table itself selects a row on the Calendar sheet, where the number entered in cell A2 (Booking sheet) corresponds to the number in the range A3:A8 (Calendar). In this example, this is cell A4
Then in line A4 (Calendar) we find the cell with the date of arrival, which is entered in cell B2 (Booking sheet) (05/15/2019) this is cell B4 (Calendar sheet), we paint it blue.
Then. It is necessary that immediately following cells after cell B4 (Calendar sheet) be painted yellow or red and the number of these cells be equal to the sum of days in cell B2 (Bookings) minus 1, i.e. if cell B2 (Booking) contains the number 10, then there should be 9 filled cells after the blue one.
Or, if it’s possible, then you can navigate by the date of departure entered in cell C2 (Bookings) (05/25/2019), i.e. it will be necessary to paint over all the cells in yellow or red, which will be in the range between cells B4 and L4. I am
trying to use formulas, but they do not work in conditional formatting
I put between and write 1 TO_DATE(VLOOKUP(A3;'booking'!A2:B;2;0))
2 TO_DATE(VLOOKUP(A3;'booking'!A2:C;3;0))
In theory, this range should be accepted for the basis and select the cells I need, but does not come out.
the table itself.
https://docs.google.com/spreadsheets/d/1Av18QnaIML...

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question