Category Archives: User Stuff

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

The Next Step Beyond the Macro Recorder in VBA Programming

I’ve been waiting for this article to get posted (not that they took too long). 2015 was a crazy year with two new books – I pushed myself beyond my limits and needed some major downtime. But out of these two new books, I’ve finally come up with some fodder for my blog. YAY!

To start – the article is on VBA Programming – a few tips for the beginner, a few tips for intermediate and some for the experienced programmer who’s looking for the next level. It ties in nicely with the new VBA book.

The second new book deals with using Excel. It’s aimed at the beginner, but really, is for anyone looking to figure out how to use a tool in Excel 2016. I was so excited when Que offered me this chance because the book has *color* pictures! This book is a step by step help file.
By the time I was done with it, I had to start trimming things out. I was so disappointed, especially since, about a month ago, I was grabbing my copy to look up something I just knew I had written the steps for. Then realized they ended up on the cutting room floor. Which made me realize – if *I* was looking for the information – so would other people. A few emails with my acquisitions editor – and I can post some of the missing material.