Forum Discussion
Conditional Data Fill and Format
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
Hi Yury,
I have no idea what your talking about. I've attached what I would like it to kind of look like. I didn't do anything to the formula, yet it gave me what I want. I don't understand why Wednesday second period has no class title, but that is what I want. I couldn't make it happen elsewhere. So Wednesday period 1 is a 2 period class, period 3 has a 1 period class. The history class happens twice and is a 1 period class. I can't find the error that is making this work. The only problem I foresee is that it is difficult to tell at a glance that in Classroom A there is a 2 period class then a 1 period class in the Art category because the 2 classes are both Art classes. Classroom A can hold any type of class, but here it has 2 art classes next to each other. Would a border around the individual classes be difficult? It would be nice to figure that out now, because it is coming in the next month. You see, there will be other spreadsheets in this workbook. They will look the same, but without the fill color and they will include other information pulled from columns in the main class list, such as AV equipment needed, Other Needs, etc. And in those spreadsheets there needs to be a border around the individual classes since there is not color fill.
It all seems so close, but too far for me to figure out.
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.