+ Reply to Thread
Results 1 to 32 of 32

Creating a three month calendar from basic table with conditional formatting

  1. #1
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Creating a three month calendar from basic table with conditional formatting

    So I've read and downloaded about 4 of the calendars user Pete_Uk has created but am having a tough time transposing my data into them.

    My wife is a romance author and I do all of her marketing. One of the key components is ad stacking i.e. multiple ad sources stacked on the same day. So it's very important to visualize what is happening on what day so everything comes together.

    I've attached a spreadsheet with some sample data and its pretty basic: Which book am I promoting? Where am I promoting it? And what date?

    Then I'd like to have three calendars for current month, next month, and month after which is about how far out I plan the ads.

    I've also made a formatting table which helps to easily visualize which books are stacked on which dates. I will probably just archive the old data for my records in a separate tab once the list starts to grow so no need to worry about anything other than the current month + next two months at a time.

    Is it possible to apply formatting like I have done manually? And if so can I add to that formatting list moving forward?

  2. #2
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    For some reason I am unable to attach my sheet... perhaps a mod has to enable that power?

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,537

    Re: Creating a three month calendar from basic table with conditional formatting

    You might have tried to use the Paperclip icon for attachments, but unfortunately this has not worked on this forum for several years.

    It would help, however, if you attached a sample Excel workbook.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    I'm sure that I could amend one of my files once I see your basic data layout.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    Wow that was confusing. Here it is hopefully
    Attached Files Attached Files
    Last edited by AliGW; 12-31-2019 at 12:30 PM. Reason: Please don't quote unnecessarily!

  5. #5
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,883

    Re: Creating a three month calendar from basic table with conditional formatting

    "j28" :
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  6. #6
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    Ooh nice thanks! That will get me the right data. Now i just need Pete_UK to work his magic on the calendar creation

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    8,997

    Re: Creating a three month calendar from basic table with conditional formatting

    How about
    Formula in I28 filled down & across
    =IFERROR(INDEX($D$3:$D$30&" - "&$E$3:$E$30,AGGREGATE(15,6,(ROW($D$3:$D$30)-ROW($D$3)+1)/($F$3:$F$30=I$27), ROWS(I$28:I28))),"")

    For Cf select I6:O59
    For violet use
    =$B$6=LEFT(I6,6)
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    Thanks! How do i make the range of the data grow dynamically? As I add more entries D3:30 will expand...
    Last edited by AliGW; 12-31-2019 at 12:28 PM. Reason: Please don't quote unnecessarily!

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    8,997

    Re: Creating a three month calendar from basic table with conditional formatting

    You can change the ranges to
    =IFERROR(INDEX($D$3:$D$200&" - "&$E$3:$E$200,AGGREGATE(15,6,(ROW($D$3:$D$200)-ROW($D$3)+1)/($F$3:$F$200=I$27), ROWS(I$28:I28))),"")
    or what ever range you need.

  10. #10
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    Oh right, haha. Is there a way in excel to have a range automatically update that I'm not aware of?
    Last edited by AliGW; 12-31-2019 at 12:29 PM. Reason: Please don't quote unnecessarily!

  11. #11
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    under the formating table you had pasted: "196-182-138"... any significance to that or just random?
    Last edited by AliGW; 12-31-2019 at 12:29 PM. Reason: Please don't quote unnecessarily!

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    8,997

    Re: Creating a three month calendar from basic table with conditional formatting

    That's just the RGB values for Love Lark, I put them there whilst setting up the CF.

    To have the range automatically expand, you can setup dynamic named ranges.

    https://www.ablebits.com/office-addi...c-named-range/

  13. #13
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    8,997

    Re: Creating a three month calendar from basic table with conditional formatting

    You can then use those named ranges in the formula like
    =IFERROR(INDEX(Bookrng&" - "&Eventrng,AGGREGATE(15,6,(ROW(Bookrng)-ROW($D$3)+1)/(Daterng=I$27), ROWS(I$28:I28))),"")
    Attached Files Attached Files

  14. #14
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,537

    Re: Creating a three month calendar from basic table with conditional formatting

    Quote Originally Posted by ryedarrow View Post
    Now i just need Pete_UK to work his magic on the calendar creation ...
    You seem to be getting some help while I've been off-line. I have some visitors staying with me this weekend, so I can't really contribute to this thread (or, indeed, any other thread) until Monday - it might all be solved by then.

    Pete

  15. #15
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,537

    Re: Creating a three month calendar from basic table with conditional formatting

    In the file that you attached to Post #4, you show the 3 months going across the page. Is this your preferred layout, or would it be okay to have the months going down the page?

    You allow up to 10 different events per day - is this sufficient, or are you likely to need to promote more books in future?

    You could have each book allocated to its own row within each day-block, for example Black could always be on the top row, then Sapphire on row 2, etc. Is this something you would like to see? It would make the CF easier to set up.

    I would envisage that you would have a drop-down to select the month and another drop-down to select the year, so that you could then easily check back on previous months. Alternatively, you could have the display automatically generated from today's date.

    I would suggest that you have your Schedule of events listed in a separate sheet, which is then your data input sheet, and show the calendar in its own sheet.

    Please get back to me before I embark on this, so I don't waste time going in the wrong direction.

    Hope this helps.

    Pete

  16. #16
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    Down the page is great! Whatever is easiest for you!

    Lets bump the events to 12/day just to give a little more room in the future.

    Sometimes I will book 5 promotions for a book on the same day so not sure if it will work locking rows. The left(5) conditional formatting offered by the other user seemed to work

    A drop down would be great so i could see past months!

    Seperate sheet is fine, that would be cleaner.

    I really appreciate your help! Make sure and include an email in the file so i can send you some beer money for your trouble!
    Last edited by AliGW; 12-31-2019 at 12:27 PM. Reason: Please don't quote unnecessarily!

  17. #17
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    Hey Pete! I'm sure you're super busy with Christmas, just wanted to circle back to make sure you saw my reply. I've got ads stacked all over the place and its a mess. After christmas can I pay you to get this done?
    Last edited by AliGW; 12-31-2019 at 12:27 PM. Reason: Please don't quote unnecessarily!

  18. #18
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,537

    Re: Creating a three month calendar from basic table with conditional formatting

    Just to let you know, I've not forgotten about this thread. I still have visitors staying with me, though, and it's my birthday early in the New Year, so I probably won't be posting much for the next two weeks or so.

    Pete

  19. #19
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    No worries brother! It's a big project I'd honestly rather just pay your hourly for it anyway I feel bad you are working on it at all in the first place over the break. Happy Birthday!
    Last edited by AliGW; 12-31-2019 at 12:28 PM. Reason: Please don't quote unnecessarily!

  20. #20
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Creating a three month calendar from basic table with conditional formatting

    How about this?

    It has each month on a separate sheet, and the ads on their own sheet.

    The calendars and the ad placement on the calendars are all done by formulas, no need to change any of that, they work off of today's date.

    The conditional formatting on the calendars is all setup, no need to change any of that.

    The ads on the AdStacking page will automatically update if changed or added to.

    The only thing you need to do is to click the Update button if you change the ad formats, change any of the formats or add another, and this will then run a procedure that updates all of the CF on the 3 calendars.
    Last edited by Bob Phillips; 12-29-2019 at 03:12 PM.

  21. #21
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    WOAH! This is incredible! Thank you!

    Two Questions:

    1) I added a new book in "Ad Stacking" B10 with formatting... I clicked "Update Calendar with Ad formats but it doesnt appear to be working, on the month tab there is no formatting applied. Did I do it incorrectly?

    2) If for some reason I wanted to look at a previous month, could I add another tab where I could input a manual month? Say input Nov 2019 or April 2020 and see what I did that month?

  22. #22
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    44,223

    Re: Creating a three month calendar from basic table with conditional formatting

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  23. #23
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,537

    Re: Creating a three month calendar from basic table with conditional formatting

    I've been working on this in the background, as time allows. I've taken a different approach to Bob, in that my file is purely formula-driven and has 2 sheets - one where you can record your events (in the style shown in your original attachment in Post #4), and the other sheet is a 3-month calendar which works from the month and year that you specify in two drop-downs on that sheet. Thus, you can select any month/year to look forwards or backwards.

    I just have to put the conditional formatting in for the events - other conditional formats have already been set up.

    Do you want me to carry on with this, or are you happy to carry on with Bob's approach?

    Happy New Year,

    Pete

  24. #24
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,282

    Re: Creating a three month calendar from basic table with conditional formatting

    Quote Originally Posted by ryedarrow View Post
    Two Questions:

    1) I added a new book in "Ad Stacking" B10 with formatting... I clicked "Update Calendar with Ad formats but it doesnt appear to be working, on the month tab there is no formatting applied. Did I do it incorrectly?
    It cannot show on the calendar until you add a new dated ad for that ad stacking in columns D:G. Update Calendar added the AD Stacking CF, but you need an ad to be using it. I added yellow Jiminy to B10, added Jiminy, Bob's Test, 1st Jan 2020 to the ads, updated the calendar, voila, all looking good.

    Quote Originally Posted by ryedarrow View Post
    2) If for some reason I wanted to look at a previous month, could I add another tab where I could input a manual month? Say input Nov 2019 or April 2020 and see what I did that month?
    The workbook you have won't do this, but it is an easy change, attached. Just copy one of the existing worksheets, update the date in A2, and it should all be fine. If you add new ads for an existing stacking, they will be catered for, if you add new ads for a new stacking, update will update all calendar sheets, no matter how many.

    The attached workbook has two new stackings, two new ads, and a new calendar Nov 2019 that I created to test this,

  25. #25
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    Huh, something on my mac doesnt like the macros you made as I tried to replicate what you said and it still doesnt seem to be working... I'll play with it on your new sheet


    EDIT

    OMG I'm so embarassed I had a typo. So sorry. The formatting worked great!
    Last edited by ryedarrow; 12-31-2019 at 07:06 PM.

  26. #26
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    Quote Originally Posted by Pete_UK View Post
    I've been working on this in the background, as time allows. I've taken a different approach to Bob, in that my file is purely formula-driven and has 2 sheets - one where you can record your events (in the style shown in your original attachment in Post #4), and the other sheet is a 3-month calendar which works from the month and year that you specify in two drop-downs on that sheet. Thus, you can select any month/year to look forwards or backwards.

    I just have to put the conditional formatting in for the events - other conditional formats have already been set up.

    Do you want me to carry on with this, or are you happy to carry on with Bob's approach?

    Happy New Year,

    Pete
    Hey Pete! I would love to see and use yours, i seem to be having some trouble with the macros in the one Bob built. Thanks for your efforts!

  27. #27
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,537

    Re: Creating a three month calendar from basic table with conditional formatting

    Okay, this is what I have so far.

    You can enter your data into columns D to G of the Events sheet in any order. Columns A and B contain formulae to put the data into a form that the Calendar can make use of, and these have been copied down to row 1000. If you think this is sufficiently far, you can hide these columns to avoid accidental deletion or overwriting of the formulae. There is no real need to delete any data, and you can just add any new events at the bottom of the list as you book them.

    In the Calendar sheet you can choose the month (by number) and year of interest in cells K5 and K6 (coloured yellow), and the calendar will automatically generate from these, showing 3 continuous months from the appropriate start date. Different colours are used to indicate the different months, and the calendar will automatically stop after the end of the three months (actually, conditional formatting hides any "excess" days). Up to 10 events can be displayed on screen for each day, but you can effectively have up to 50 events per day and choose which block of 10 events to show by using the drop-down in cell K1.

    The calendar picks up the data from the Events sheet and places it under the appropriate day. This is all done automatically by the formulae, so the only things you need to play with are the three yellow cells. I'm using the data that you originally posted, which is for December 2019, so you can choose different month/year combinations to see this move about. Feel free to add more data in the Events sheet and then see that positioned appropriately.

    The outstanding item is to set up the conditional formatting for each event, so I'll finish that off when I get chance, and post back.

    Hope this helps.

    Pete
    Attached Files Attached Files

  28. #28
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,537

    Re: Creating a three month calendar from basic table with conditional formatting

    I managed to find some time yesterday morning to complete the conditional formatting, but I was holding back a bit to allow you to comment on the file attached to Post #27, to see if there were any other amendments needed.

    In the absence of any comments, I've decided to attach the completed file. The panel in column K of the Calendar sheet acts like a key to the colours. A new one (red with a yellow foreground) highlights today's date if it exists within the 3 months that are displayed.

    All the other features are as described above - enter data in the Events sheet, then use the yellow cells in the Calendar sheet to control what is displayed.

    I didn't know that you were using a Mac - the colours might appear slightly different for you.

    Hope this helps.

    Pete

  29. #29
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    Oh wow this is fantastic! Incredible what you can do in excel!

    I like the formatting in column K, what is the best way to add a new book/format to the list as my books expand over time?

  30. #30
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    Maybe I would need a macros for that like in Bob's? ...could I add that to yours as I like the 3 month stacked view better.

  31. #31
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,537

    Re: Creating a three month calendar from basic table with conditional formatting

    I've not looked at Bob's file in detail, so I'm not sure how he generates the formatting.

    It is a little bit awkward to do this manually, as the conditional formatting needs to be applied to all cells in the calendar sheet (except for the rows where the dates appear). Essentially, though, you can do this in one operation by selecting all 10 rows for a particular week, e.g. from B5 to H14, then holding down the CTRL key and selecting the next week's cells (i.e. B16 to H25), then releasing CTRL, and so on for all the other weeks in the calendar, and then you would click on Conditional Formatting | New Rule | Use a formula... , and then your formula would be something like this:

    =LEFT(B5,6)="Yellow"

    (Note that the 6 shown in red is the number of letters in the keyword [in this case "Yellow"], and this would change to 4 if the word was "Grey", for example). Then you would click on the Format button | Fill tab to set the background colour, then click on the Font tab | Font Styles box to select Bold, and then on the Color drop-down to select the foreground colour that you want. Then you can just OK your way out and Excel will automatically adjust the cell reference from B5 to suit all the other cells that had been selected.

    Then you would just add that particular colour combination (manually) to the key in column K.

    Hope this helps.

    Pete

    P.S. If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can show your appreciation directly and thank those members who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  32. #32
    Registered User
    Join Date
    12-13-2019
    Location
    SLC, UT
    MS-Off Ver
    MSFT Mac
    Posts
    16

    Re: Creating a three month calendar from basic table with conditional formatting

    Holy Smokes that worked! THANK YOU THANK YOU! I will mark as solved right now. Wow you just saved my life on this stuff. DM your email so I can send you something nice.

+ 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. Replies: 2
    Last Post: 04-05-2019, 02:34 PM
  2. Replies: 1
    Last Post: 12-29-2018, 07:22 PM
  3. Replies: 2
    Last Post: 10-03-2016, 08:35 AM
  4. help creating basic pivot table...
    By yertleturtle in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 02-18-2014, 12:22 PM
  5. Creating table with conditional formatting based on growing contents
    By Ash_ in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-13-2013, 11:45 AM
  6. Basic Conditional Formatting
    By bighorn2 in forum Excel General
    Replies: 19
    Last Post: 04-24-2012, 05:27 PM
  7. Creating a new Table based on Conditional Formatting in another table
    By JPKenny in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-06-2010, 11:48 PM

Tags for this Thread

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