Forum Discussion
Conditional Data Fill and Format
Hi Douglas,
please find attached my suggested solution. I have done the following steps:
1. Replaced the formula in I2 (lookup string) with '=E2&"|"&F2&"|"&G2'
2. Removed _ from the Room drop down list to match the headers in the 'Color Block' sheet
3. Unmerged range A7:A11 and placed 'Wednesday' in each cell of the range. This is helpful in designing consistent formula in the class schedule
4. Added a column J in the 'Main Class List' to concatenate Class Name and Instructor using the following formula: '=A2 & CHAR(10) & B2'. Note, that I used the CHAR(10) function to push the instructor's name to the next line. For this to work, the destination cell must have 'Text Wrap' setting turned on (as I have done for the range D7:H11 of the 'Color Block' sheet)
4. Placed the following formula in D7, and copied it across to the range D4:H11 of the 'Color Block' sheet: =IFERROR(INDEX('Main Class List'!$J$2:$J$15,MATCH($A7&"|"&$B7&"|"&D$6,'Main Class List'!$I$2:$I$15,0)),"")
5. Activated cell D7 of the 'Color Block' sheet, then added a formula driven conditional formatting (Home>Conditional Formatting>New Rule>Use formula...), then placed the following formula in the box: =INDEX('Main Class List'!$D$2:$D$15,MATCH($A7&"|"&$B7&"|"&D$6,'Main Class List'!$I$2:$I$15,0))="Art". Subsequently, I defined blue background colour, and applied the formula to the range '$D$7:$H$11'. You would have to follow this pattern to add background colours for other class types
Hope this helps. Please let me know if you have any queries
Yury
Thanks a million Yury,
That nearly completes my work. There is one thing I hadn't added yet and I hope it doesn't screw up the way the formulas work. I don't know if I should have a separate column that is a drop down where one can choose the number of periods of the class duration. Some classes will last 2 periods and some will last 4. I don't think any last 3. So I can either have them pick 1, 2, 3, 4 periods in a separate column or in the period column have choices that correspond to 1 & 2 period, 2 & 3 period, 3 & 4 period, 1 & 2 & 3 & 4. Using something besides & sign will probably help. I somehow think that it will be easier to list the multiple periods in the period column. I will still have a column that shows class periods of class duration, just so it is easy to see.
The reason I want to populate the chart is so that people don't go changing it and not the list. The list is also going to populate other charts that combine the information listed. I think with what you have shown me, I can figure out those sheets.
Hi Douglas,
The easiest way to handle the task would be to use Power Query (Get & Transform embedded in Excel 2016+). If you have Excel 2013 or 2010 you will need to download it from https://www.microsoft.com/en-au/download/details.aspx?id=39379
Please see the example file attached, where I used Power Query.
I have converted the data range into a table, added 4 columns to enter period numbers applicable to the class, then loaded the table into Power Query and applied a few transformations including removal of some columns, unpivoting of period columns and replicating the concatenated lookup fields (so you do not require these in the data input table anymore). Then, I have loaded the query result into the 'Data' tab, and connected all formulas and conditional formats to the query table.
After entering your class details, please refresh the query to update results (either Data>Refresh All, o right click on the table in the 'Data' tab, then select 'Refresh'.
Hope this helps
Yury
I still can't figure out how to make the conditional formatting formula not only format the cell to which the formula is applied but if certain conditions are met then format other cells:
For example, see formula below:
- If the number 2 is in a column of the row where the information in this cell comes from then format the cell beneath current one the same; If 3 then format current cell and 2 cells beneath the same; if 4 then 3 cells...and if the number 1 then just format current cell.'
say the information for the cell is pulled from row 2 and the number from column H in that row.
=INDEX('Main Class List'!$D$2:$D$15,MATCH($A7&"|"&$B7&"|"&D$6,'Main Class List'!$I$2:$I$15,0))="Art"
Somewhere in this formula it needs to seek out a number from the row where it picked up this information and then have some condition clause to know which cells to format.
Hi Douglas,
it would be a very complex formula, as each cell would need to test if all previous entries for the day have number of periods to be reflected in the cell. The easiest way would be to create a district data row for each of the cell in the class schedule. This can either be achieved by manually duplicating class data for each of the periods, or using Power Query to create distinct rows based on setting of which period the class falls to. I would recommend creating a field, data from which can be pulled into the schedule, which can be set to be blank for subsequent periods on the day. This way, the respective part of the schedule can be conditionally formatted, while showing no text. I can design the query to do the job, if you prefer. Please just let me know.
Many thanks
Yury
Wow, that is complicated. But it won't work for my needs. It looks like a class that is more than 1 period is offered several periods rather than one long period. And it the list of classes you have it listed several times, as if it were offered several times. Some classes are offered several times and will be listed separately, but others are are more than 1 class period and sometimes offered more than once. I can't tell from the results that this method gives which is which.
I think if I keep the formulas you gave me the first time and somehow add a conditional formatting rule that says - if duration is 2 then format the cell beneath the same color as the first cell of the class. I don't want text in the other cells, just the first cell. So if the duration is 3 periods then use the same formatting for the 2 cells below. And then 4 periods. And perhaps it would need to have if duration is 1 period than format only the current cell. Not sure about that.
Is this possible? The only problem I foresee is if a class changes. Say a 2 period class goes in and the 2 cells get a red background and then we decide to move it. Wouldn't the lower cell remain red? Maybe not. If I move a class now the cell where it was goes back to no fill.