Conditional Data Fill and Format

Question

I have a Workbook with several sheet.&nbsp; One sheet has data listed in columns, such as:Class, Instructor, Type, Classroom, Day, Time PeriodThe Type, Classroom, Day, and Time Period Columns are drop down lists.&nbsp; The Class and Instructor column are manually entered text.&nbsp;The other sheet is a chart with days and times&nbsp; in rows (Wednesday Column A takes up 5 rows and each of those rows in a time period, such as 8am-9am, 10am-11am - all in Column B),&nbsp; &nbsp;The columns following these are each a separate classroom:&nbsp; Classroom A, Classroom B, etc.&nbsp;I would like to fill out my chart sheet automatically based on the selection made in my list sheet.&nbsp; For example, if I have this in my list:Basket Weaving&nbsp; |&nbsp; Mr. Weaver |&nbsp; Art | Weaver Hall | Wednesday |&nbsp;Period 1 |&nbsp;I would like the cell that corresponds to Wednesday, period 1, Weaver Hall to automatically fill in the Class name and Instructor and change its background color based on the type of class (art = blue).&nbsp; Where to I begin to make this happen - marcos, formulas?&nbsp; I've attached a sample file&nbsp;What I've done so far is to define names for the rows and columns on the chart(e.g., Wednesday1, Classroom_A)Then I use a cell at the end of the line of data on the class list to get my 2 coordinates time &amp; place.&nbsp; It gets this data from the cells for that class so it will say in Column I - Wednesday1 Classroom_A.&nbsp; Then I'm stuck.&nbsp;Many thanks in advance for any help.Douglas

yury tokarev · Answer

Hi Douglas,&nbsp;please find attached my suggested solution. I have done the following steps:&nbsp;1.&nbsp;Replaced the formula in I2 (lookup string) with '=E2&amp;"|"&amp;F2&amp;"|"&amp;G2'&nbsp;2. Removed _ from the Room drop down list to match the&nbsp;headers in the 'Color Block' sheet&nbsp;3. Unmerged range A7:A11 and placed 'Wednesday' in each cell of the range. This is helpful in designing&nbsp;consistent formula in the class schedule&nbsp;4. Added a column J in the 'Main Class List' to concatenate Class Name and Instructor using the following formula: '=A2 &amp; CHAR(10) &amp; 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)&nbsp;4. Placed the following&nbsp;formula in D7, and copied it across to the range D4:H11 of the 'Color Block' sheet:&nbsp;=IFERROR(INDEX('Main Class List'!$J$2:$J$15,MATCH($A7&amp;"|"&amp;$B7&amp;"|"&amp;D$6,'Main Class List'!$I$2:$I$15,0)),"")&nbsp;5. Activated cell D7 of the 'Color Block' sheet, then added a formula driven conditional formatting (Home&gt;Conditional Formatting&gt;New Rule&gt;Use formula...), then placed the following formula in the box:&nbsp;=INDEX('Main Class List'!$D$2:$D$15,MATCH($A7&amp;"|"&amp;$B7&amp;"|"&amp;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&nbsp;follow this pattern to add background colours for other class types&nbsp;Hope this helps. Please let me know if you have any queriesYury&nbsp;&nbsp;

douglas laing · Answer

Thanks a million Yury,&nbsp;That nearly completes my work.&nbsp; There is one thing I hadn't added yet and I hope it doesn't screw up the way the formulas work.&nbsp; 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.&nbsp; Some classes will last 2 periods and some will last 4.&nbsp; I don't think any last 3.&nbsp; 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 &amp; 2 period, 2 &amp; 3 period, 3 &amp; 4 period, 1 &amp; 2 &amp; 3 &amp; 4.&nbsp; Using something besides &amp; sign will probably help.&nbsp; I somehow think that it will be easier to list the&nbsp; multiple periods in the period column.&nbsp; I will still have a column that shows class periods of class duration, just so it is easy to see.&nbsp;&nbsp;The reason I want to populate the chart is so that people don't go changing it and not the list.&nbsp; The list is also going to populate other charts that combine the information listed.&nbsp; I think with what you have shown me, I can figure out those sheets.&nbsp;

yury tokarev · Answer

Hi Douglas,&nbsp;The easiest way to handle the task would be to use Power Query (Get &amp; Transform embedded in Excel 2016+). If you have Excel 2013 or 2010 you will need to download it from&nbsp;https://www.microsoft.com/en-au/download/details.aspx?id=39379&nbsp;Please see the example file attached, where I used Power Query.&nbsp;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,&nbsp;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.&nbsp;After entering your class details, please refresh the query to update results (either Data&gt;Refresh All, o right click on the table in the 'Data' tab, then select 'Refresh'.&nbsp;Hope this helpsYury&nbsp;

douglas laing · Answer

Wow, that is complicated.&nbsp; But it won't work for my needs.&nbsp; It looks like a class that is more than 1 period is offered several periods rather than one long period.&nbsp; And it the list of classes you have it listed several times, as if it were offered several times.&nbsp; 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.&nbsp; I can't tell from the results that this method gives which is which.
&nbsp;
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.&nbsp; I don't want text in the other cells, just the first cell.&nbsp; &nbsp;So if the duration is 3 periods then use the same formatting for the 2 cells below.&nbsp; And then 4 periods.&nbsp; And perhaps it would need to have if duration is 1 period than format only the current cell.&nbsp; Not sure about that.
&nbsp;
Is this possible?&nbsp; The only problem I foresee is if a class changes.&nbsp; Say a 2 period class goes in and the 2 cells get a red background and then we decide to move it.&nbsp; Wouldn't the lower cell remain red?&nbsp; Maybe not.&nbsp; If I move a class now the cell where it was goes back to no fill.

douglas laing · Answer

I still can't figure out how to make the conditional formatting formula&nbsp; not only format the cell to which the formula is applied but if certain conditions are met then format other cells:&nbsp;&nbsp;
For example, see formula below:
&nbsp;-&nbsp; 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.
&nbsp;
=INDEX('Main Class List'!$D$2:$D$15,MATCH($A7&amp;"|"&amp;$B7&amp;"|"&amp;D$6,'Main Class List'!$I$2:$I$15,0))="Art"
&nbsp;
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.

Forum Discussion

Conditional Data Fill and Format

Share

Resources