Hi - Dave here.
Happy Friday!
This week, I want to share a simple solution to a classic hard problem in Excel: how to combine data on multiple sheets with a formula.
In the workbook below, the goal is to combine data on Sheet1, Sheet2, and Sheet3 on a summary sheet. When ranges are at a known location, a nice way to do this is to use the VSTACK function with a 3D reference like this:
=VSTACK(Sheet1:Sheet3!B5:E16)
[
Download the workbook and read the full explanation]
The 3D reference makes it easy to expand the formula to include more sheets. If the ranges contain blank rows you want to exclude from the final result, you can remove these rows with the FILTER function (as above). The article at the link above provides the complete formula. Download the workbook and try it out yourself.
Note: the VSTACK function is only available in Excel 365 for now.
Excel formulas
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
