Hi, I have some data in Column A. In Column B2 I type: A2:A15 and in C2 I type =INDIRECT(B2), in Excel 365 I get the data that is referenced with a thin blue frame, in Excel 2016 I get the #Value! error. Does this not work in Excel 2016 or is there some trick to it or error on my part?
Try:
=INDIRECT("B2")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
For me, on Excel 2016.
It's work when press Ctrl-Shift-Enter for array formula.
Regards.
My English is very poor, so please be patient >_<"
My Dynamic Related Dropdown post:
https://www.excelforum.com/excel-for...ml#post5657376
https://www.excelforum.com/excel-for...ml#post5655711
https://www.excelforum.com/excel-for...ml#post5655762
This shows the text that is written in Cell B2: literally "A2:A15"Originally Posted by Glenn Kennedy
I attached two screenshots, I tried the Ctrl-Shift-Enter method, the formula gets enclosed in curly brackets and I only get the first value of the array, in this case: "1" - if I drag the formula down, it says #VALUE! ...
The first image is with Show Formulas option enabled. Light one is 2016 - Dark one is 365 ( And 2016 is in German, so it is not a typo it is spelled with K)
what do you want to do with the range ?
if you use 'range' in the indirect it's can only show the first (or last) element in the range.
(while the result will be different in o365)
Regards.
You could try:
=INDEX(INDIRECT($B$2),ROWS($C$2:C2))
And then copy down. It will show values for however many are in the range in B2 (so in the example, for the first 14 rows), and then show a #REF! error in subsequent rows. You could wrap it in an IFERROR to make it show blank, if that is desired.
I think we're all missing the point here (except possibly for menem!!). WHY do you want to do this? In 99% of times when someone uses INDIRECT there is a far better way of achieving the result.
Can you show us (on a sheet... not on a picture of one) what you are wanting to DO with the values in A2:A15.
Ok I was interested in how Indirect works in gernal. Lets say the lists in my example on Sheet1 and Sheet2 will grow in the future, I do an Index(Match()) to see what items are not on either one Sheet. I wanted to use the Indirect function. It was mainly to A) understand INDIRECT and B) to shorten the formula INDEX(MATCH())
Using INDIRECT is a bit like playing with nitroglycerine... unless you have to... don't.
Uncomplicated MATCH formulae (i.e. without additional criteria) work perfectly well with whole column references. I'd use:
=IF(B2="","",IF(ISNUMBER(MATCH(B2,Sheet1!A:A,0)),B2,B2&" is not in Sheet 1"))
and
=IF(Sheet1!A2="","",IF(ISNUMBER(MATCH(Sheet1!A2,Sheet2!B:B,0)),Sheet1!A2,Sheet1!A2&" is not in Sheet 2"))
You could use this one in D3:
=B2&IF(COUNTIF(Sheet1!A:A,B2),""," is not on Sheet1")
Copy down as required.
Hope this helps.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks