Learn to create and manipulate ‘3D Named Ranges’ in Excel (Includes a Bonus VBA Code)

A named range usually takes input from one single sheet, but excel provides you the option to create a 3D Named Range as well, which takes input from multiple sheets and perform required operations on the collected data. A 3D Named Range is particularly useful in scenarios where you have multiple data points for a single item, in multiple sheets, and you want to add all data points. For ex, you have sales figures for 3 financial years in 3 separate sheets, and you want to combine all sales values together in a single cell, in an output sheet. Or suppose, you have separate sales sheets for several products and you want to combine them together in one sheet. For all such cases, a 3D Named Range is the solution. But creating and manipulating 3D Named Range is a bit time consuming, So as a bonus tip, I have included a VBA code in the attached file that can manipulate the 3D Named Ranges, on your behalf, in a single click.

Timestamps:
0:00 Introduction to Named Ranges
1:56 How to create a 3D Named Range
4:31 A practical example of using 3D Named Ranges
8:12 VBA Code to change data source (‘Refers to’) for all Named Ranges in a single click

Link for practice file:
https://drive.google.com/file/d/1RiBg...