Returning the current row in a Table

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.

1-24-2017-image-1

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:

ROW()-ROW(tablename[#Headers])

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):

1-24-2017-image-2

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s