The following formula refers to the transpose of the current cell:
=indirect("R"&column()&"C"&row(), False)
Indeed, it takes the row and column numbers of the current cell and swaps them to get a cell reference in R1C1 format.
However, having to paste this in every row of upper-half of the matrix separately is not appealing. (One can't select a triangular region to put the formula in). So here is a better way:
Suppose your current matrix is in the sheet named "matrix". In cell A1 of another sheet, enter
=if(isblank(matrix!A1), indirect("matrix!R"&column()&"C"&row(), False), matrix!A1)
and extend this formula to a square region the size of the matrix. You get a symmetric matrix in that sheet: the formula takes either the matching cell in sheet "matrix" or its transpose, whichever is nonempty. Then select the matrix and copy values only (Ctrl-Shift-V) back into the original sheet.
Alternatively (if you don't want to mess with Indirect, maybe because your matrix is off-center and you'd need some adjustments to row/column numbers), use three sheets: one for lower half matrix, one for its transpose, and one more to combine them:
=if(isblank(matrix!A1), transpose!A1, matrix!A1)
Short answer
The problem is the xPath, specifically the /tbody
part as it's not present in the source code.
Explanation
The source html code of the page doesn't include the tbody tag. It's added by the browser when it is missed to create a DOM hierarchy but the algorithm behind IMPORTXML doesn't do the same, it requires the xPath for the original source code of the web page.
Removing it from the xPath will solve the problem.
The following formula returns the value of OPEN INT, 141
:
=ImportXML("http://eoddata.com/stockquote/OPRA/WFM160826P00032000.htm",
"//*[@id='ctl00_cph1_qp1_div1']/div[2]/table[1]/tr[2]/td[5]/font/b"
)
References
https://stackoverflow.com/questions/938083/why-do-browsers-insert-tbody-element-into-table-elements
Best Answer
I see what the Asker is getting at, and I have an answer and some advice:
First, I should warn you, that down this path lies madness.
It may seem like something obvious to do, and it doesn't look that hard, but generating HTML using a spreadsheet (be it Google Sheets, Excel or any other spreadsheet package) may seem simple, but things can and will get over-complicated, or you will very soon reach a point in which you will need to write a macro to achieve your desired output.
Moreover, once you take the plunge and start writing your first couple of macros, if your sheet is not already overly complicated, you will hit the limits of the macro language pretty soon too.
But why? I'm not doing anything too intricate!
The reason you will quickly exhaust the features of a spreadsheet when using it to generate HTML is that generating structured markup is not well suited to the overall design of grid calculation paradigm. Which is the reason we have Word and Excel and that
Word processing is in itself, basically the process of generating a structured markup document. So there are software packages more suited to the task.
So what would be a wise thing to do?
My advice would be to keep the database in the spreadsheet, (a task for which it is well suited) and spare yourself deep frustration and unmet deadlines, and invest some time to learn a proper language; one that is easy to learn and efficient at templating HTML.
Python comes to mind. The alternative being taking advantage of the features of other Google products (since you're already doing Sheets) like Google Sites that makes it dead easy to create proper websites, and allows to embed and drive some of its pages from the data in a spreadsheet.
But I want to do it in Sheets! In fact, that's why I am asking here!
Agreed. However, I've been down that road, and would have appreciated that someone warned me that madness awaited, so I felt it would have been irresponsible and unfair to the Asker not to warn him of the perils ahead. (I'm looking at those who know, and didn't raise a voice.)
That being said, I've seen people abuse spreadsheets in manners far more troubling. So:
Here is my shot at good advice on the design of a structured-markup generating spreadsheet
You can see all this advice in action in this Google Spreadsheet, so you can examine the actual formulas at your leisure and see how these simple points will help you avoid creating an unmaintainable monstrosity and keep your sanity for a while more.
For the named ranges, choose names that look like the markup they reference so your formulas are readable and maintainable, like say
Complexity will creep up quickly, keep your formulas simple and build the markup progressively from the inside out, taking advantage of using formulas in adjacent cells. for example, in column C surround a plain value with inner <p> tags, in column D, surround the value from C in outer <span> tags, in column E, surround whatever is in D with outer <div> tags and so on. one step at a time, you've got plenty of columns and then some
Please do visit the example sheet since that will be a lot clearer that a gazillion screenshots or the dense prose I tend to write when I get all worked up. Hope this really helps!