In a conversation with Bill Jelen about the TEXTJOIN function, he mentioned whether I had tried using 3D references as arguments. I said that I had not tried native 3D ranges, and I did not believe it would work. Well, I was wrong (again). It does work, in spite of there being no documentation from Microsoft about that ability. The figure below shows the result in cell D2 of using this formula:
=TEXTJOIN(“,”,,Sheet1:Sheet2!A1:A2)
I continue to be amazed by the myriad of formula solutions made possible by the TEXTJOIN function. In a recent article where I demonstrated the use of non-contiguous ranges
I now realize that the same concept can be used with 3D ranges. (Here is the bonus!) In fact, that 3D range can be in ANOTHER workbook, as shown below.
=TEXTJOIN(“,”,,Sheet1:Sheet2!A1:A2,[TEXTJOIN_Native3DB.xls]Sheet1:Sheet2!A1:A2)
The example file can be downloaded here (Note: I did not include the external file, and I put a tilde in front of that formula)
Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.
Pingback: via #Excel Native 3D Ranges with the TEXTJOIN Function Plus Bonus by David Hager — Excel For You – SutoCom Solutions
Pingback: #Excel Super Links #48 – shared by David Hager | Excel For You
Pingback: Revisiting Using the Excel TEXTJOIN Function To Return Unique Items From A 3D Range by David Hager | Excel For You
Pingback: #Excel For You Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You
Pingback: #Excel Impossibly Easy #1: Return a 1D Array from Non-Contiguous Native 3D Ranges | Excel For You
Pingback: #Excel Super Links #150 – Special Edition | Excel For You
Pingback: #Excel Data Validation – Non-Contiguous Ranges and Changing Data Validation List after Picking | Excel For You