+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting and Automation Help

  1. #1
    Registered User
    Join Date
    07-29-2015
    Location
    Inglis, MB
    MS-Off Ver
    2013
    Posts
    11

    Conditional Formatting and Automation Help

    Hi there Excel Questions helpers!
    I've got myself confused and stuck.
    Below is a link to the spreadsheet where we need these 3 actions to work:

    Data tab - When #nights has a value in it (any value that is not null), then all cells must highlight (so from B to O). I can get 1 cell to highlight, but I can't get the whole row to do it. For each cell without doing it individually for each cell too.

    On the remaining weekly tabs, if a date in the data tab is in that week, then it need to be populated in it's corresponding week. So for example Eriksdale School has 70 students coming on Dec 19th. This needs to appear on the Dec 18-24 tab under Tuesday Dec 19th. I'm really stuck on this. Is that an If the dates match, then hlookup?

    I think i've attached the spreadsheet within this, please let me know if it isnt!

    Thank you kindly!!
    Hannah
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Conditional Formatting and Automation Help

    Hi HStollery,

    See the attached where I've done the Conditional Formatting part of your problem. I don't understand the second/third part of the question.

    CF for Row based on Cell.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Conditional Formatting and Automation Help

    On the remaining weekly tabs, if a date in the data tab is in that week, then it need to be populated in it's corresponding week. So for example Eriksdale School has 70 students coming on Dec 19th. This needs to appear on the Dec 18-24 tab under Tuesday Dec 19th. I'm really stuck on this. Is that an If the dates match, then hlookup?
    I am not clear. What "This" is that needs to appear. Sample data shows what appear to be text "time" slots with no time data in the 'Data' sheet.

    Can you clarify?
    Dave

  4. #4
    Registered User
    Join Date
    07-29-2015
    Location
    Inglis, MB
    MS-Off Ver
    2013
    Posts
    11

    Re: Conditional Formatting and Automation Help

    Thank you MarvinP! That is perfect!

    MarvinP and FlameRetired - Sorry for the confusion!!

    For the weekly tabs, what I'm trying to achieve is I would like the school name to appear on the corresponding weekly sheets tab.
    What I'm trying to achieve is reducing the amount of times we enter data. So I figured I can input all the information into the Data tab, which would the flow into the weekly tabs.
    With that example, in the data tab, Eriskdale school is coming on Dec 19th for 1 night, bringing 70 students - all the other information is populated as we get it. I would like to have that information flow automatically into the weekly tabs. So on the Dec 18-24 tab, under Tuesday Dec 19, the school name should appear then have the number of students next to it. There is an Example tab which displays this.

    I thought of doing an IfThen Statement with an HLookUp - I wasn't sure if that was correct as I couldnt get it to work.

    Does that make any more sense at all?

    Thank you guys!

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Conditional Formatting and Automation Help

    Thank you. Yes it does.

    For the weekly tabs, what I'm trying to achieve is I would like the school name to appear on the corresponding weekly sheets tab.
    It will be simpler if the tab names also include the year. Would that be acceptable?

  6. #6
    Registered User
    Join Date
    07-29-2015
    Location
    Inglis, MB
    MS-Off Ver
    2013
    Posts
    11
    Quote Originally Posted by FlameRetired View Post
    Thank you. Yes it does.

    It will be simpler if the tab names also include the year. Would that be acceptable?
    O ya that is totally fine. I'm completely open to suggestions to make it work

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Conditional Formatting and Automation Help

    It turns out you already had the years in the reference dates row 4.

    Try this in A5:N5 and fill down as far as needed. It returns the School names and MULT.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Copy and paste into the other tabs or instead group the tabs and enter all at once from tab 'Dec 18-24'.

    If you are not familiar with grouped tabs ... click on the first date range tab and while holding down the Shift key click on the last tab. They will be grouped. Enter the formula as above and each sheet will have it's own copy automatically.

+ 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. Conditional Formatting Removing Previous Conditional Formatting?
    By CravingGod in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-04-2016, 01:02 PM
  2. Replies: 6
    Last Post: 01-08-2016, 06:44 PM
  3. Automation: applying conditional formatting to group of cells in a loop
    By ×_× in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-21-2014, 12:11 PM
  4. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  5. Replies: 1
    Last Post: 09-20-2013, 06:23 PM
  6. Gantt Chart Help with Conditional Formatting Automation
    By starbwoy in forum Excel General
    Replies: 9
    Last Post: 12-15-2009, 06:45 PM
  7. Repetitive Spreadsheet Formatting Automation
    By excel addict in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2006, 02:35 AM

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