Log In
Register
Home
Active Member
September 18, 2020
Offline
I have watched your Dashboard video and found it very helpful, thank you ! I have set up a simple Dashboard to show a project timeline and used conditional formatting/colour coding to show projects on the time line.
I have 2 issues I cant seem to resolve:
Issue 1:
I would like to show the projects with a status of 'Completed' for historical data reasons but I want the scroll bar and sequence formula to use the projects that are 'Accepted or 'In Progress' only. Obviously overtime the date range will blow out if it does not take the status into consideration. I used the formula as shown in your video, which is: =SEQUENCE(1,26,MIN(B5:B5)+'Data Entry = Project Budget'!O2,1) in cell I4 of the spread sheet. It has been suggested to use a "COUNTIF to populate the SEQUENCE function's columns argument" - I have attempted this but my formula returns a #CALC error.
Issue 2:
When new rows are added to my table in the Dashboard it does not automatically update the conditional formatting I have in place. After researching for hours, I have tried everything without finding a resolution.
I have attached my file for reference, any advice/instructions you can provide will be much appreciated. Thank you in advance
Leanne
October 5, 2010
Offline
Hi Leanne,
No file attached 🙁
You need to click on the upload button
https://www.myonlinetraininghu.....and-guides
Regards
Phil
Active Member
September 18, 2020
Offline
Sorry, file attached
July 16, 2010
Offline
Hi Leanne,
The SEQUENCE function is only available in Excel for Microsoft 365. If you're getting the #CALC! error, maybe it's because you don't have the function in Excel. When I opened your file the SEQUENCE formula didn't return an error.
There is an alternate formula in the video description as follows:
For those who don't have the SEQUENCE function, you can use two formulas instead:
Cell K5: =MIN( start date column ) + output of form control
Cell L5: =K5+1 Copy L5 across columns as far as required.
In regards to your conditional formatting, the columns I:AH are not part of the table. You need to set up the conditional formatting to allow for growth in the 'Applies to' range. e.g. set it to I5:AH500 if you think you'll have that many rows.
Mynda
Active Member
September 18, 2020
Offline
Hi Mynda
Thankyou for your response. I am using Microsoft 365 and yes there is no error on the formula used (as per your video) in =SEQUENCE(1,26,MIN(B5:B5)+'Data Entry = Project Budget'!O2,1). The error occurs when I attempt to include the COUNTIF formula as I only want I4 to return dates for line items that have a status of 'In Progress' or 'Accepted'. I am trying to work out how to use the "COUNTIF to populate the SEQUENCE function's columns argument".
I have changed the range to I5:AH500 (this appears to be the only suggestion I could find on the internet) in the "applies to" and the conditional formatting still does not auto populate a new row. The only way I can get the fields to populate is to copy and paste the entire line (I5 to AH) from the row above which is not user friendly.
I have attached the file again which shows the range has been changed to I5:AH500
Leanne
July 16, 2010
Offline
Hi Leanne,
Change MIN to MIN(IF...:
=SEQUENCE(1,26,MIN(IF((Table14[Status]="Accepted")+(Table14[Status]="In Progress"),B5:B7))+'Data Entry = Project Budget'!O2,1)
For the conditional formatting, you'll notice when you add a new row to the Table, the cell references in the formulas in columns I:AH also shift down, so now the references are out of alignment with the table. This is because when a new row is added to a table, Excel effectively inserts a row in the worksheet and any formulas referencing rows that weren't part of the table, automatically adjust to follow the original cell.
The solution is to use a PivotTable to create the data in columns A:H (as I did in my example) because even if you move the formulas in columns I:AH to the Conditional Formatting rules, it too will adjust the formulas when new rows are added to the table. You can still use the Table, just put it on another sheet and reference it as the source data for your PivotTable.
Hope that helps.
Mynda
1 Guest(s)
