Conditional Formatting and Automation

[Hannah_Asessippi]

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?

the link to it is: https://asessippi.com/wp-content/uploads/Example-SS.xlsx I hope I've done the link correctly (please let me know if this isn't allowed or if I should be doing this differently).
Thank you kindly
Hannah

 

[NiMip]

Re: Conditional Formatting and Automation Help

I can help with the first bit, but the second might take a bit more thinking. I'm also unsure about the rules around links to spreadsheets.

For the conditional formatting, you'll want to select from the Rule Types "Use a formula to determine which cells to format" and type in the formula =$E2<>"" Note that I've locked down the column with a $ but not the row, so the condition will travel down the rows of data but always be determined by the value of #NIGHTS in column E.
Then for the range the formatting applies to, enter the full data table you want the colouring to occur for, $B$2:$O$20

 

[Hannah_Asessippi]

Re: Conditional Formatting and Automation Help

Hi NiMip,

I'm so 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?

 

[NiMip]

Re: Conditional Formatting and Automation Help

No you explained it perfectly fine, I was just struggling to come up with a way to do it
Bit of a clunky solution and I have no doubt there is a smarter way, but the following works. Paste the formulas in the cells I've specified, and then copy and paste the cell (or drag the formula) across the other cells. The formulae should following the dates and such.

On any weekly tab...
In cell A6: =IFERROR(IF(COUNTIF(Data!$B:$B,A$4)>=ROWS(A$6:A6),INDEX(Data!$C:$C,MATCH(A$4,Data!$B:$B,0)+ROWS(A$6:A6)-1)," ")," ")
In cell B6: =IF(A6<>" ",SUMIFS(Data!$F:$F,Data!$B:$B,A$4,Data!$C:$C,A6)+SUMIFS(Data!$G:$G,Data!$B:$B,A$4,Data!$C:$C,A6),"")

Assuming you want the Day 2 part like in the example tab...
In cell A21: =IFERROR(IF(COUNTIFS(Data!$B:$B,A$4-1,Data!$E:$E,">0")>=ROWS(A$21:A21),INDEX(Data!$C:$C,MATCH(A$4-1,Data!$B:$B,0)+ROWS(A$21:A21)-1)," ")," ")
In cell B21: =IF(A21<>" ",SUMIFS(Data!$F:$F,Data!$B:$B,A$4-1,Data!$C:$C,A21)+SUMIFS(Data!$G:$G,Data!$B:$B,A$4-1,Data!$C:$C,A21),"")

To sum the sit ski bookings...
In cell B31: =SUMIF(Data!$B:$B,'Dec 18-24'!A$4,Data!$M:$M)

NOTE:All of this only works if the dates added on the Data tab are in order. It finds the number of entries on the given day of the year, and if there is more than one entry for that day, then it expects subsequent entries will be in the rows directly one after the other.
EDIT: I forgot to mention, this also won't work for your Edward Schreyer School entry, as the date isn't actually a date, but text describing two days. It would only work if these were entered as two separate entries.

 

[Hannah_Asessippi]

Re: Conditional Formatting and Automation Help

Hi NiMip, those calcs work amazingly thank you!! I've made the adjustments to the dates in the Data tab as well (I had missed those incorrect ones unfortunately)/

One thing however... I forgot we have 3 day schools...

I've changed the spreadsheet to factor in new information which will make it easier to distinguish the 1 Days to the Multi Days (Column I). I've readded the link here - the link is the same, I just replaced the SS.
https://asessippi.com/wp-content/uploads/Example-SS.xlsx

So the scenario is similar to that of a 2 Day school (that formula works perfectly right now!). But as with the Example Tab, you'll see Vincent Massey needs to appear on Tuesday, Wednesday and Thursday.

I've applied the formula into the Dec 18-24 tab, but Vincent Massey appears under Tuesday and Wednesday but not Thursday.

Is that a hard trick to fix???

Thanks,
Hannah