Forum Discussion
Conditional Data Fill and Format
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
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.