Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
You must save this as a Macro-Enabled Workbook, or create an add-in with specifics for a certain file.
I chose the simpler method of using a Macro-Enabled Workbook.
I am making a marathon ranking which has a raw data table where contestants' times are inserted manually and a dashboard table where lists are sorted by various categories and ages and by best time.
It have 2 sheets :
- the main sheet (RAW) where a user will complete data in the raw data Table named Tbl_raw. First you have to create such a Table (select all data with headers then press CTRL+T to create the Table)
- the dashboard sheet (DASHBOARD) where there will be a Table named Tbl_dashboard that updates based on Tbl_raw changes (we will create this below, do not create it manually)
Then I will use an excel native function called Relationships located on the Data tab, towards the middle-right part of the ribbon, a bit after Data Validation button.
The advantage of having this "relationship" is that any change you make to Tbl_raw will reflect in Tbl_dashboard, but not the other way around. So I can add multiple columns to Tbl_dashboard without changes being done in reverse to Tbl_raw.
Now, there are 2 things that must be done in order for this to work:
1. Create a table based on another table
- in your RAW sheet, the Tbl_raw Table containing your data must have been created - in here a user will manually insert data, in my case in the following columns: contestant number, name, surname, gender, race type, age interval, start semi, start cross, recorded race time
- go to the Data tab, under the Get & Transform Data group click on the rightmost button called Existing Connections, and in there click on the Tables tab
- select the Tbl_raw table and click on the Open button and an Import Data dialog box should appear
- make sure Table is selected, choose the desired location of the table (eg. New Worksheet), and - this is very important - make sure the "Add this data to the Data Model" checkbox is selected
- in the same dialog box, by clicking the Properties button you can choose when your connection will refresh, I let mine with default setting - "Refresh this connection on Refresh All"
- now I pressed the OK button on the Import Data dialog box and a new worksheet was created containing a table which is a clone of the first, having seemingly the same contents, but you will notice that any formulas Tbl_raw had, they are no longer in here - I named this new table Tbl_dashboard
2. Create a relationship between tables - this works in Excel 2013 or newer
- now that we have or 2 tables connected to each other, we must create a relationship between them to give more power to our connected tables
- go to the Data tab and on the ribbon towards the middle-right side, a bit after the Data Validation button, press the Relationships button and a box called Create Relationship will pop
- click on New, and on the left side select your 2 tables in each dropdown
- on the right side of the Create Relationship box a column that does not have duplicate values, for example I cannot select the column Name for this relationship because I will receive an error telling me that "Both selected columns contain duplicate values. At least one of the columns selected must contain only unique values to create a relationship between the tables.
- so I don't select the Name column in both, but instead select the recorded time column in the second dropdown and Name in the first, then click OK, and Close
Result
Now that we have a table based on another table, and a relationship between them, whatever we write in Tbl_raw will be reflected in Tbl_dashboard upon refresh.
To test, add a new line at the end of Tbl_raw containing some data. Then go to Tbl_dashboard, right click on it and select Refresh, and the newly inserted row in Tbl_raw will also appear in Tbl_dashboard.
If when on the Import Data box you selected Properties and changed the Refresh settings to say "Refresh every 1 minute", then your tables will refresh every minute, but you might have to wait, or the refresh might occur when you don't need it to. Lemme explain.
In Tbl_dashboard I want to add a couple of columns to automatically sort my table by recorded time, smallest being best. I want to rank my contestants to show the winner, 2nd place, 3rd place and so on.
To do that, I create 2 new columns in Tbl_dashboard, one named "PLACE" and one named "MEDAL".
The PLACE column will have the following array formula (CTRL+SHIFT+ENTER):
=SUM(IF(SUBTOTAL(103; OFFSET( [RECORDED TIME];ROW([RECORDED TIME])-ROW($J$2);0;1 ))>0;IF([@[RECORDED TIME]]>[RECORDED TIME];1)))+1
Note: this will display contestants from best time to worse, if you want this in reverse change the >0 in the formula above to <0
The MEDAL column will contain the following formula:
=IFERROR( IFS([@PLACE]=1;"GOLD";[@PLACE]=2;"SILVER";[@PLACE]=3;"BRONZE");"")
I also add some Conditional Formatting to color my top 3 rows in the colors of gold, silver and bronze (gold fill = #D6AF36, silver fill = #D7D7D7, bronze fill = #977547)
Then I sorted the table by PLACE column to show 1st place first, then 2nd place and so on. This sorting will remain and when the table updated based on new data in Tbl_raw, it will automatically sort itself as desired upon doing the Refresh option.
However, one wants the table to be updated automatically rather than having to refresh manually.
There are 2 options for this, both involving VBA. So I saved my file as a Macro-Enabled file. To be honest, I use both options in my file as I want both an automatic and an optional / manual option at hand.
1. Create a manual refresh button that is easier to click in the sheet containing the Tbl_dashboard table. A visible shape called REFRESH NOW linked to the following VBA code stored in a module or within the sheet code:
Sub Rfrsh()
' note that Sheet3 is the raw Excel name of the DASHBOARD sheet, Sheet3 won't change regardless of the name you give that sheet, cause if you hardcode Sheets("DASHBOARD") into VBA, then change the sheet name, the VBA code will error
Sheet3.ListObjects("Tbl_dashboard").TableObject.Refresh
End Sub |
2. Create an automatic refresh when you activate the DASHBOARD sheet. This must be created under the DASHBOARD sheet code (right click - View Code)
Private Sub Worksheet_Activate()
Dim TB, TBdash As ListObject
Set TB = Sheet2.ListObjects("Tbl_raw")
Set TBdash = Sheet3.ListObjects("Tbl_dashboard")
'at first I wanted it to refresh only when last row of Tbl_raw was different from last row of Tbl_dashboard
Dim LR, LRdash As Long
'LR = TB.Range.Rows.Count
'LRdash = TBdash.Range.Rows.Count
' this checks if the value for recorded time has been added, because if it is empty and you refresh Tbl_dashboard, your 1st, 2nd and 3rd place will be messed, as empty becomes the best time
If TB.Range.Cells(LR, TB.ListColumns("RECORDED TIME").Index).Value <> "" Then
TBdash.TableObject.Refresh
End If
Set TB = Nothing
Set TBdash = Nothing
'LR = 0
'LRdash = 0
End Sub |
Now this last code still isn't perfect, improvements should still be done to take into account all that could happen. Haven't thought of everything yet, but I will update the code when I have.
_______________________________________
|
|
