Feb 20 2019 07:57 AM
Hi,
I was looking for help with the following offset question, thanks for any help:
Using the current offset formulas:
cell reference A20 = 1, B20= 3, C20= 7, D20= 9, E20=14, F20= 15
OFFSET(A20,0,4,1,1) Result - 14
SUM(OFFSET(A20,0,4,1,2)) Result - 29
Question
is there away to have the width give the value instead of summing, ( The value returned would be E20 & F20) Result 14 15 |
Feb 20 2019 03:51 PM
Any idea if this can be done, or is the question not clear? thanks
Feb 20 2019 08:10 PM
SolutionHi,
There is an easy solution using TEXTJOIN function as follows:
=TEXTJOIN(", ",TRUE,OFFSET(A20,0,4,1,2))
But, please note that the TEXTJOIN is only available in Excel 2019 or Office 365
If you have an earlier version of Excel, you will not see this function!
For earlier versions of Excel, I would suggest this workaround using INDEX and concatenation operator:
=INDEX(OFFSET(A20,0,4,1,2),1)&", "&INDEX(OFFSET(A20,0,4,1,2),2)
Hope that helps
Feb 20 2019 08:10 PM
SolutionHi,
There is an easy solution using TEXTJOIN function as follows:
=TEXTJOIN(", ",TRUE,OFFSET(A20,0,4,1,2))
But, please note that the TEXTJOIN is only available in Excel 2019 or Office 365
If you have an earlier version of Excel, you will not see this function!
For earlier versions of Excel, I would suggest this workaround using INDEX and concatenation operator:
=INDEX(OFFSET(A20,0,4,1,2),1)&", "&INDEX(OFFSET(A20,0,4,1,2),2)
Hope that helps