#Excel Native 3D Ranges with the TEXTJOIN Function Plus Bonus by David Hager

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)

TJ3D2.PNG

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

https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/

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)

TJ3D1.PNG

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)

TEXTJOIN_Native3D

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.

Advertisements

6 thoughts on “#Excel Native 3D Ranges with the TEXTJOIN Function Plus Bonus by David Hager

  1. Pingback: via #Excel Native 3D Ranges with the TEXTJOIN Function Plus Bonus by David Hager — Excel For You – SutoCom Solutions

  2. Pingback: #Excel Super Links #48 – shared by David Hager | Excel For You

  3. Pingback: Revisiting Using the Excel TEXTJOIN Function To Return Unique Items From A 3D Range by David Hager | Excel For You

  4. Pingback: #Excel For You Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  5. Pingback: #Excel Impossibly Easy #1: Return a 1D Array from Non-Contiguous Native 3D Ranges | Excel For You

  6. Pingback: #Excel Super Links #150 – Special Edition | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s