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?