Author Archives: tsyrstad

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.


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


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.

Excel workbook slow to open

Jump to the solution

I usually open files by double-clicking on them. I rarely use the File, Open route of the parent program. Imagine my surprise, when out of the blue, a file a double-clicked on did NOT instantly open!! Matter of fact, I double-clicked on it 3-4 times. Was there something wrong with my mouse? No. Other files opened normally. And wow – the file (well, files, since I tried so many times) did eventually open up. Time to troubleshoot:

  1. Open Excel directly – works as normal
  2. Go File, Open, choose Excel file – works as normal
  3. Right-click file, choose Open With – Excel – slooooow
  4. Unload any Add-ins and COM Add-ins – no change
  5. Check other Office applications (like Word) – works as normal
  6. Do an Office repair – no change
  7. Try Safe Mode – no change

At this point – I’m getting frantic. Excel is my job. I do NOT like the thought of rebuilding my PC. One last shot.

Few people realize how much Excel depends on your operating system’s default print driver**. Excel calculating slowly or acting unusual can sometimes be fixed by changing the default print driver. I decided to check, in case it had mysteriously been changed. Nope, same driver. But….. so I pick another configured printer (my TechSmith Snagit printer – any will do, even a virtual one), double-clicked a file, crossed my fingers – and Excel popped open like normal! YAY!

But, of course, I need my normal driver, so I switched my default printer back. I also turned the printer on – I’ve had it off a few days, which is unusual. And Excel still works properly. Is turning the printer back on a crucial step? I’ll know in about a week as I’ve turned it back off and will post an update if this happens again.

*phew* Catastrophe averted.

**this is NOT a setting in Excel. It’s a Windows (or Mac) system setting