I have three subtotals columns and a subtotal at the top of the Excel row 1 for each column. It reads the subtotal of each columns subtotal. The formulas all work if each of the sub-totals have a value.
The problem: if one of the many VLookup returns a #N/A, the Grand subtotal for that entire column of subtotals returns a #N/A
Is there a way to add a conditional option in the Excel Subtotal function?
I only want to Subtotal values that are numeric and just ignore the #N/A in a column.
I use Access 2003 and SQL Server 2005 to group data.
Then with Excel automation, I create a Workbook.
One Worksheet has all the Natural Gas Buy contracts, another the Natural Gas Sell contracts for a time period determined by the user interface.
From there, a very custom daily report with 3 columns of VLookup and subtotals managed by the VBA code, record counts, and so on.
I can change my VLookup code to an (If(vlookup finds a matching contract put price, else put zero) My preference is to leave the #N/A to indicate that there is not a matching contract, rather than put the value zero.
Bookmarks