Consolidating sheets without Power Query

Рет қаралды 1,610

9 ай бұрын

I recorded my live attempt at the final question of the South African Excel Championship (I'm not South African, so not an actual participant, but the organizers were kind enough to let me play along). This case is The Mzansi Hotel Group, by Renier Wessels, and it's all about managing / consolidating data across lots of sheets (specifically, to measure occupancy and revenue for a chain of hotels).
The case was designed to be tackled with Power Query to consolidate data from many different sheets, but this video shows how you can do simple consolidations just with formulas - as long as you have modern Excel!
You can also watch the question design team discuss their various approaches to the case here:
• South African Excel Ch...

Пікірлер: 10

@Simon-vc1wk 7 ай бұрын

Amazing

@RenierWessels 9 ай бұрын

Thanks Dim. I enjoyed your solution. That IF(SEQUENCE trick was a new learning for me. Thanks for taking the time!

@DimEarly 9 ай бұрын

That's worth remembering - very handy for adding the labels when you unpivot anything (i.e. not just multi-sheet stuff like this). Thanks for the case, it was a good one!

@RenierWessels 9 ай бұрын

@@DimEarly thanks Dim 🙏🏻

@sledgehammer-productions 9 ай бұрын

So now it's *Dark* magic? I'll follow you to any light intensity 😉

@DimEarly 9 ай бұрын

😂

@drkazza5533 8 ай бұрын

This was great - but have you ever tried CTRL-# quick formatting to dd-mmm-yy would have made the bit at 17:00 much cleaner to work out what month it is. Given I work with sheets from Americans and RoW it's been a lifesaver

@DimEarly 8 ай бұрын

Yes, I use it all the time! The challenge here was that I was comparing one date formatted as a date (which meant it adapted to my settings) with another date that was part of a text string (which meant that it didn't). I misinterpreted the fact that the dates were in 'my' format to mean that they had been written that way, but it was just Excel adapting to my settings.