I have to admit, when Tables first came out all those years ago, I didn’t pay any attention. I didn’t see an advantage to them – what they did, I could do in other ways, as I had always done.
Fast forward to about two years ago – and I’m a bit of a Table Junkie (thank you Zack and Kevin). If I can include a Table – I will. But, sadly, Microsoft hasn’t kept up with the popularity of Tables, especially programming with them. But that’s another story. Even for the straightforward Excel user, there are limitations to Tables. And I hit one pretty hard the other day.
I’m using a Table for an invoice table. Each line item can have its own packaging cost, so the row includes setting up custom costs for various packaging types, such as Bulk Truck, 50lb Bags, etc.
It’s easy enough to find the correct column (use MATCH), but if I use the ROW() function to get the current row, it would return the row number based on the sheet, not the location of the Table, which did not start in A1. The rest of the formula is specific to the Table. The solution is to include the location (row) of the Table. Basically:
This take your current row on the sheet (assume 17) and subtracts from it the row number of the Table’s header row (16). 17-16 = 1, which is the first data row (and my current row) of the Table.
The entire formula in my Table looks like this:
=IFERROR(OFFSET(tblInvoice[[#Headers],[Item ID]],ROW()-ROW(tblInvoice[#Headers]),MATCH([@[Unit Type]],tblInvoice[#Headers],0)-1),0)
The Table looks like this (the formula would go in the selected cell):