+ Reply to Thread
Results 1 to 14 of 14

Populating specific cells with a specific value based on dates entered on another sheet

  1. #1
    Registered User
    Join Date
    09-29-2015
    Location
    CA
    MS-Off Ver
    2013
    Posts
    21

    Populating specific cells with a specific value based on dates entered on another sheet

    Hi Everyone,
    I was hoping someone could help me out. I have a attached a very basic layout of what I am trying to do. With that said after looking at it. I have two worksheets inside the workbook. One is titled " ENTRY" the other "CALENDAR". My goal is to take the information from the "ENTRY" sheet and populate the respective cells on the sheet called "CALENDER". I have tried a number of different formulas and so far the closest i have got is with:

    =IF((OR(AND(ENTRY!$B3:$B5000=$A4), AND(ENTRY!$G3:$G5000>=CALENDAR!$B2), AND(ENTRY!$H3:$H5000<=CALENDAR!$B2)), ENTRY!$E3:$E5000, "SHOP")


    My goal here is to have excel recognize the value I have entered into ENTRY!B:B, If the Value on CALENDER!A:A is of equal value then I want to populate the cells on the respective row on CALENDAR! with the value ENTRY!C:C from the same row that the matching value is from. However I only want the value from Entry!C:C to populate the cell that fall with in the date range that has been specified in ENTRY!D:E.

    Thank you all for your help.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,311

    Re: Populating specific cells with a specific value based on dates entered on another shee

    JB20,

    Think the attached does what you want?

    You can test it by changing the Units, Jobs and dates in the Entry sheet.

    It uses an "Index/Match" array (entered with Ctrl+Shift+Enter) against the three criteria:

    {=IFERROR(INDEX(ENTRY!$C$3:$C$8,MATCH(1,IF(B$2>=ENTRY!$D$3:$D$8,IF(B$2<=ENTRY!$E$3:$E$8,IF($A4=ENTRY!$B$3:$B$8,1)),0))),"SHOP")}

    (Look in Col C of the Entry form and find the row where the date in Col D is more than or equal to the date in Row 2 of the Calander sheet, the date in Col E is less than or equal to it, and the Data in Col B matches the row in Col A of the Calender sheet, If there is no match, put "Shop" in the cell,)

    Hope it helps

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 04-26-2016 at 09:30 PM.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,739

    Re: Populating specific cells with a specific value based on dates entered on another shee

    Would you prefer the Calendar sheet to look like a monthly calendar, i.e. with day blocks across (Sunday to Saturday) and then 4 or 5 weeks down for each month, with the appropriate entries shown on the appropriate day? You could select the month and year of interest using drop-downs.

    Pete

  4. #4
    Registered User
    Join Date
    09-29-2015
    Location
    CA
    MS-Off Ver
    2013
    Posts
    21
    Quote Originally Posted by Ochimus View Post
    JB20,

    Think the attached does what you want?

    You can test it by changing the Units, Jobs and dates in the Entry sheet.

    It uses an "Index/Match" array (entered with Ctrl+Shift+Enter) against the three criteria:

    {=IFERROR(INDEX(ENTRY!$C$3:$C$8,MATCH(1,IF(B$2>=ENTRY!$D$3:$D$8,IF(B$2<=ENTRY!$E$3:$E$8,IF($A4=ENTRY!$B$3:$B$8,1)),0))),"SHOP")}

    (Look in Col C of the Entry form and find the row where the date in Col D is more than or equal to the date in Row 2 of the Calander sheet, the date in Col E is less than or equal to it, and the Data in Col B matches the row in Col A of the Calender sheet, If there is no match, put "Shop" in the cell,)

    Hope it helps

    Ochimus
    Thank you so much. This has been plaguing me for a while.

    This is exactly what I was looking for. I had a feeling it was an array formula. I am however running into another issue. When the start date is any date after 1/1/2016 the job name populates the cells from 1/1/2016 until the end date. For example if job 1 is from 2/1/2016 - 3/28/2016; the calender sheet will populate the respective cells from 1/1/2016 - 3/28/2016. Any thoughts?

    Thanks again for all your help

  5. #5
    Registered User
    Join Date
    09-29-2015
    Location
    CA
    MS-Off Ver
    2013
    Posts
    21
    Quote Originally Posted by Pete_UK View Post
    Would you prefer the Calendar sheet to look like a monthly calendar, i.e. with day blocks across (Sunday to Saturday) and then 4 or 5 weeks down for each month, with the appropriate entries shown on the appropriate day? You could select the month and year of interest using drop-downs.

    Pete
    Pete,
    I am always game for learning new and alternative ways. If you dont mind Id like to see what your talking about.

    Thanks again.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,739

    Re: Populating specific cells with a specific value based on dates entered on another shee

    Okay, I played about with it this afternoon. I've added some formulae to your Entry sheet, and put a bit more test data in - this can be in any order, and if you leave the final date off (as in the last example) then it will assume it is only for one day. In the Calendar sheet you can choose a month and year from the drop-downs in column K and the display will automatically adjust, giving you up to 12 Units for each day.

    Hope this helps.

    Pete
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-29-2015
    Location
    CA
    MS-Off Ver
    2013
    Posts
    21
    Quote Originally Posted by Pete_UK View Post
    Okay, I played about with it this afternoon. I've added some formulae to your Entry sheet, and put a bit more test data in - this can be in any order, and if you leave the final date off (as in the last example) then it will assume it is only for one day. In the Calendar sheet you can choose a month and year from the drop-downs in column K and the display will automatically adjust, giving you up to 12 Units for each day.

    Hope this helps.

    Pete
    Thanks this is great. Unfortunately the end result im trying to get will not work in this format. At least not for this specific application. However I do know where i can use this. Thanks a ton for this info.

    If I could just figure out the problem I explained in my response to "ochimus" I would be golden.

    Thank you again everyone for your help.

  8. #8
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,311

    Re: Populating specific cells with a specific value based on dates entered on another shee

    JB,

    Been "AFD" since I posted the example, hence no earlier reply.

    I tried it with a later date, and found exactly the same problem, which baffles me at the moment because the formula is quite explicit that it should produce the "right" result only if the column date is on or between the dates in Cols D and E of the Entry sheet.

    I'm out shortly, but will get onto this tomorrow morning - although knowing this site I suspect you have several replies before then spotting what I have missed!

    Ochimus

  9. #9
    Registered User
    Join Date
    09-29-2015
    Location
    CA
    MS-Off Ver
    2013
    Posts
    21
    Quote Originally Posted by Ochimus View Post
    JB,

    Been "AFD" since I posted the example, hence no earlier reply.

    I tried it with a later date, and found exactly the same problem, which baffles me at the moment because the formula is quite explicit that it should produce the "right" result only if the column date is on or between the dates in Cols D and E of the Entry sheet.

    I'm out shortly, but will get onto this tomorrow morning - although knowing this site I suspect you have several replies before then spotting what I have missed!

    Ochimus
    Ochimus,
    Ive been playing around with it this morning and ive figured out that all the cells on the earlier dates, that should not be populated are capturing the last value listed in column C on ENTRY!. Any ideas why this would be happening?

    Thanks again for all your help.

    -JB

  10. #10
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,311

    Re: Populating specific cells with a specific value based on dates entered on another shee

    JB,

    Cracked it, thanks to JohnTopley, who laid the foundations in another thread on here earlier this month.

    Formula as follows, and it's not even an Array!

    =IFERROR(INDEX(ENTRY!$C$3:$C$8,MATCH(1,(ENTRY!$B$3:$B$8=$A4)*(B$2>=ENTRY!$D$3:$D$8)*(B$2<=ENTRY!$E$3:$E$8),0)),"Shop")

    But there is one intriguing problem.

    If you look at Units 5 and 6, the Entry sheet has Start and End dates, but no Job marked against them.

    If the Calander column is outside those parameters, the formula puts "Shop" in the cell, because there is no "match".

    But in the Columns within the date range, it enters "0",because that is how it interprets the blanks in Col C of the Entry sheet!

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 04-29-2016 at 05:27 PM.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,739

    Re: Populating specific cells with a specific value based on dates entered on another shee

    That is an array formula - you need to commit it using Ctrl-Shift-Enter (CSE), and you can see curly brackets around the formula when viewed in the formula bar.

    If you want to avoid seeing 0 when the Job is blank, you can do this in B4:

    =IFERROR(INDEX(ENTRY!$C$3:$C$8,MATCH(1,(ENTRY!$B$3:$B$8=$A4)*(B$2>=ENTRY!$D$3:$D$8)*(B$2<=ENTRY!$E$3:$E$8),0))&"","Shop")

    (changes in red). Use CSE to confirm the formula, then copy across and down.

    Hope this helps.

    Pete

  12. #12
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,311

    Re: Populating specific cells with a specific value based on dates entered on another shee

    Pete,

    Can't think why I said it wasn't an array when every cell in my attachment EXCEPT B4 was arrayed - just my luck that the original still produced the correct output!

    I also left the "0" in deliberately, as I didn't know whether JB wants to distinguish between Units where the column was within the date span but no job was assigned, and those where the date was outside the span, whether or not there was a job?

    Your update is definitely an improvement on my original, because it gives JB a simple way to apply whichever approach is correct.

    Ochimus

  13. #13
    Registered User
    Join Date
    09-29-2015
    Location
    CA
    MS-Off Ver
    2013
    Posts
    21

    Re: Populating specific cells with a specific value based on dates entered on another shee

    Pete & Ochimus,
    You guys are awesome. Sorry for the delay, my computer took a turn for the worse and i just go it back up and running. Thanks for all the help. As far as seeing "0". I can just create a conditional format to either match the character color and the background color, or I can simply go to the advance settings and check the box that allows the worksheet to display zeros. Once again Thank you guys so much you both have just expanded my knowledge of excel beyond where I ever thought i would be at. Massive Props to the both of you.

    -JB

  14. #14
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,311

    Re: Populating specific cells with a specific value based on dates entered on another shee

    Appreciated, just glad we could help you out. It'll be your turn in a few years time!

    Ochimus

+ 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. [SOLVED] Macro to copy specific data from one WB to specific cells in another WB based on specific
    By d_rose in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-26-2014, 11:05 AM
  2. [SOLVED] Rename tabs based on values entered in specific cells
    By rshnkmr39 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-13-2013, 06:55 AM
  3. [SOLVED] Populating mutiple cells based on specific values
    By Grillpan in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-14-2012, 09:12 AM
  4. HELP! Macro to Copy specific cells from one sheet to another based on specific criteria
    By atriscritti in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-11-2012, 11:05 AM
  5. Replies: 0
    Last Post: 07-11-2012, 07:19 PM
  6. Replies: 1
    Last Post: 12-14-2011, 11:32 PM
  7. Need to copy specific cells into an existing worksheet based on date entered by user
    By jrfleury in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2011, 09:44 AM
  8. Replies: 15
    Last Post: 10-11-2009, 11:46 AM

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