3

Been tearing my hair out today with this one. I'll be as concise as I can.

I have a number of named ranges, and I would like to reference them in a long, ugly formula that works without them just fine. However things get weird when I start to switch to named ranges.

The original, working formula:

=INDEX(IMPORTHTML("http://finance.yahoo.com/quote/"&B38&TEXT(I38,"yymmdd")&H38&TEXT(SUBSTITUTE(J38,".",""),"0000000")&"0","table",1),3,2)

The values in use are:

B38: COH
I38: 09/16/16
H38: P
J38: 41.00

It constructs the proper URL and imports the desired table using index(). All is well. The final URL is http://finance.yahoo.com/quote/COH160916P00041000

But now I start adding named ranges rather than cell references and things go sideways.

This fails with an error: =INDEX(IMPORTHTML("http://finance.yahoo.com/quote/"&Symbol&TEXT(Expires,"yymmdd")&Trade_Type&TEXT(SUBSTITUTE(Strike_Price,".",""),"0000000")&"0","table",1),3,2)

All of the named ranges are valid and get properly color coded, but I get the error:

Function INDEX parameter 2 value is 3. Valid values are between 0 and 1 inclusive.

Now to make things more interesting... I can put this in another cell:

="http://finance.yahoo.com/quote/"&Symbol&TEXT(Expires,"yymmdd")&Trade_Type&TEXT(SUBSTITUTE(Strike_Price,".",""),"0000000")&"0"

And it constructs a perfectly proper URL that works fine. I can then reference that cell from my original formula like so:

=INDEX(IMPORTHTML(AB37,"table",1),3,2)

and it all works perfectly.

So what on earth is going on here? Why won't my named ranges work directly in the initial index(importhtml()) formula?

Improve this question
1
  • 1
    In order to be able to reproduce the problem, add the values of the reference or add a link to a demo spreadsheet having only the formulas and the named ranges and the corresponding values..
    – Rubén ♦
    Aug 10, 2016 at 11:34

1 Answer 1

Reset to default
2

Regarding why INDEX does not work when there are named ranges as parameters, but does work in the other cases, it is not clear to me yet.

I was thinking that the problem is that INDEX requires that its first parameter be a reference to a spreadsheet range, as it's stated in the official documentation.

Besides the formula forms that you already found, you could try to use QUERY instead of INDEX. Example:

=QUERY(
  IMPORTHTML(
    "http://finance.yahoo.com/quote/"&
      Symbol&
      TEXT(Expires,"yymmdd")&
      Trade_Type&
      TEXT(SUBSTITUTE(Strike_Price,".",""),"0000000")&
      "0",
    "table",
    1
  ),
  "select Col2 where Col1='Bid'",
  0
)

Bid is the label for the third row, so I used it to get the value at row 3, column 2.

There are other alternatives but they will depend on the use case an your personal preferences as a spreadsheet user.

Improve this answer
8
  • Ah very interesting, I didn't know about query() so thanks for that! So my index() version will work when the first parameters is a sheet reference, even if that sheet reference is the result of importhtml(), but it won't handle a named range directly. Very interesting... thanks for the explanation! Oh and it works great in my application btw. =)
    – JVC
    Aug 10, 2016 at 20:53
  • Now that I read your comment, I think that I should think again about my response :)
    – Rubén ♦
    Aug 10, 2016 at 21:06
  • @JonathanvanClute: Is you found helpful my answer, please remember to upvote it.
    – Rubén ♦
    Aug 10, 2016 at 22:14
  • Oops, thanks I always forget to do that!
    – JVC
    Aug 10, 2016 at 22:40
  • I stand corrected, your solution works perfectly in some places, and not at all in others. I don't understand. This formula - =query(IMPORTHTML("http://finance.yahoo.com/quote/"&Symbol&TEXT(Expires,"yymmdd")&Trade_Type&text(substitute(Strike_Price,".",""),"0000000")&"0","table",1),"select Col2 where Col1='Bid'", 0) works on some cells and not others, when filled down. When it fails, the error is Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col2. How can this be when the column definitely exists??
    – JVC
    Aug 11, 2016 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.