Forum Discussion
Consolidation question
How do I consolidate and analyse data from five sheets, all with the heading project but with some entries missing on some sheets? The spreadsheet has 215 rows in total but when I consolidate with mi...
There are two obvious routes. The first is to use Power Query and Join the Tables by the Project. This will refresh on demand. The other option is to use a lookup formula
= XLOOKUP(Table1[Project],Table2[Project],Table2[Manager],"")This calculates immediately any change is made to one of the input Tables.
et
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Start", type date}, {"End", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Project"}, Table2, {"Project"}, "Table2", JoinKind.LeftOuter),
#"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Manager"}, {"Table2.Manager"})
in
#"Expanded Table2"- Thanks Peter
