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?

  • 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 '16 at 11:34
  • Data has been added, thanks! – Jonathan van Clute Aug 10 '16 at 14:36
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.

  • 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. =) – Jonathan van Clute Aug 10 '16 at 20:53
  • Now that I read your comment, I think that I should think again about my response :) – Rubén Aug 10 '16 at 21:06
  • @JonathanvanClute: Is you found helpful my answer, please remember to upvote it. – Rubén Aug 10 '16 at 22:14
  • Oops, thanks I always forget to do that! – Jonathan van Clute Aug 10 '16 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?? – Jonathan van Clute Aug 11 '16 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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