I feel I'm way out of my league on this one, but I thought I would give it a try, because it needed to be done.
I have to retrieve data from a colleagues departmental Excel file, where each row is a different project, this department only collects a single phase of the project’s timeline. The data collection cycle on this phase is about 4 months, multiple locations and seasons.
Right now the spreadsheet goes out to column CY and divided into sheets by year, so to fill in data you have to scroll a lot of columns to get to where you need to be to enter data and again each row is a different project.
I would like to break it apart by work phase, allowing for easy of data entry but keep the relationship of all of the phases with a unique ID.
I'm in the agriculture industry, so please bear with me. I have tentatively set up the 6 phases (Planning, Planting, Field, Harvest, Cleaning, Testing), analysis can be done with pivot tables or Power BI.
The Planning phase is where the unique ID (identified by ** in the attachment) will be setup.
How do I departmentalize or break this apart, but cascade the primary data to the other phases? Can it even be done in Excel?
I have been playing with Power Query, but nowhere near proficient enough to figure it out, reason I have turned to you
Thank you
Doug
Hi Doug,
You're on the right track wanting to split this data up. As long as each dataset has a common ID you can split this task into separate tables and then use Power Pivot to create relationships between the tables allowing you to analyse the data in PivotTables.
You will need to structure the data in a tabular format though, which means only one row of headers, not the headers you currently have split across multiple rows.
I hope that points you in the right direction.
Mynda
Thank you, I have made the header column one row and moved the year sheets into one sheet with a column that designates the year of the project.
When I break these into different tables, I'm trying to figure out how the key fields can be cascaded to the other tables without having to enter the same data in again. (see attachment of tables).
The team will fill out the "Planning" table first, then at planting time, they will need to have the fields from the "Planning" table cascaded to the other tables.
If I can't figure out how to cascade the data, then I found this on YouTube that might be another solution, maybe not the best one, but a solution.
Keep all of the columns in one wide sheet and used the following VBA to show certain columns depending on a selection of the phase they want to view or edit. (this would hide columns)
This VBA has to be modified to do what I had in mind.
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("B1"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Yield": Columns("I:L").EntireColumn.Hidden = True
Columns("E:H").EntireColumn.Hidden = False
Case Is = "Field": Columns("I:L").EntireColumn.Hidden = False
Columns("E:H").EntireColumn.Hidden = True
End Select
End If
End Sub
If nothing else, this is a good exercise for learning.
Hi Doug,
Hmmm, one option is to use formulas to automatically bring in the fields into the other tables, but formulas are risky as they're easily broken. A better option would be to have one table and lots of columns. Instead of the VBA, you can use group and outline buttons, as explained here.
Mynda
