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.
