I am making a calendar that changes color based on the value entered and having information filled in using a separate table. I want to have a formula in the calendar that can adjust and enter the value that corresponds to the day in the full table no matter where the day starts.
For example my calendar can have day 1 of the month start on a Sunday so when you input the value into the fill table for day 1, the colander picked up that value. However if day 1 changes to start on Wednesday, it should not matter since the formula will still look for day 1 on the fill table and put an the appropriate value.
Attached is a sample sheet of what I am using. The calendar has the rows divided into a day above the box I want to value to populate for. For each box below the day of the month, I want to info for the fill table under the "Rate" column to populate into the calendar as long as the day on the calendar matches the table "day"
I have tried a variety of formula's like If, if/and as well as index but I can't seem to get it. Please let me know what is the best approach for this.
I am doing this on google sheets if it's relevant.
Bookmarks