+ Reply to Thread
Results 1 to 6 of 6

Have a calander formula that matches the date to a page to populate info

  1. #1
    Registered User
    Join Date
    08-06-2017
    Location
    Glendale, California
    MS-Off Ver
    excel
    Posts
    57

    Have a calander formula that matches the date to a page to populate info

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Have a calander formula that matches the date to a page to populate info

    Your "numbers" in col-AH are text that just look like numbers, meaning they cannot be directly matched with the numbers on row-2. I fixed col-AH to be real numbers.

    You have merged columns B:E, F:I etc. Is there some value to you that I'm not seeing? I can't see any value and this will unnecessarily complicate any formulas and there are numerous reasons in general why merged cells are a bad idea. I have removed them - hopefully that works for you.

    With these changes then (relative to the attached workbook) in B3 copied across to H3:
    =INDEX($N4:$N34, MATCH(B2, $M4:$M34, 0))
    Now copy B3:H3 to B5:H5, B7:H7 etc.

    The attached update to your workbook implements the above.

    Let us know if this is at least a step in the right direction and whether it needs additional work.
    Attached Files Attached Files
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  3. #3
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Have a calander formula that matches the date to a page to populate info

    Not sure if this is what you want.
    But take a look with the vlookup formula and a few changes i made
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  4. #4
    Registered User
    Join Date
    08-06-2017
    Location
    Glendale, California
    MS-Off Ver
    excel
    Posts
    57

    Re: Have a calander formula that matches the date to a page to populate info

    Thank you GeoffW283 so much for the help. That formula was perfect!

  5. #5
    Registered User
    Join Date
    08-06-2017
    Location
    Glendale, California
    MS-Off Ver
    excel
    Posts
    57

    Re: Have a calander formula that matches the date to a page to populate info

    Thank you Popipipo for the help here! This works as well. An interesting switch of formula's really shows me the unique methods to this problem.

  6. #6
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Have a calander formula that matches the date to a page to populate info

    Thanks for the feedback and rep

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to Auto Populate Info from Table to Calendar based on date
    By sbattle18 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-03-2020, 09:10 PM
  2. How to Auto Populate Info from Table to Calendar based on date
    By sbattle18 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-31-2020, 04:42 PM
  3. [SOLVED] If date matches and room matches, return room info in to cell
    By HonorBray in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-12-2018, 12:09 PM
  4. Filter info by date parameters and populate listbox
    By Raylou in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-31-2014, 09:31 AM
  5. [SOLVED] Need formula to find matches, then populate other cells
    By damobilebrood in forum Excel General
    Replies: 4
    Last Post: 09-05-2012, 08:38 AM
  6. Replies: 2
    Last Post: 06-25-2010, 09:19 AM
  7. search from page a for date in pages b,c,d,and paste info in page e
    By belkin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2009, 05:44 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1