Wednesday, October 10, 2007

Dynamic Reports In Excel using HTML

We use XSLT to generate reports and typically the destination is Excel. So the report is generally basic HTML tables and Excel will parse the table info. Today, I needed to sum an amount column but had no way of knowing how many rows were in the result. After spending considerable time on Google, I came away with this:
=SUM(A1:INDIRECT(ADDRESS(ROW()-1,1)))

Seemed pretty intuitive once I found it. The problem with this is hitting the right combination of keywords when searching. So hopefully, by describing my problem here and the solution, I have narrowed someone else's search a bit.