Tag Archives: Excel

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

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.

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