Excel Sheet Navigation - No Macros
March 3, 2020
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
Put this link at the top of an Excel sheet, so you can quickly go to the first empty cell for data entry, without using any macros.
Cell B1 has a HYPERLINK formula, with 2 arguments:.
We want to go to a cell in column A, in the same file, so the link starts with "#A"
Then, to calculate the row number for the next empty cell, get a SUM of the table's starting row, the number of data rows in the table, plus 1, to get to the next row.
=HYPERLINK("#A" & SUM(COUNTA(tblDays3[Day]), ROW(tblDays3[[#Headers],[Day]]), 1), "Add Entry")
Or, use cell references, instead of table references:
=HYPERLINK("#A" & SUM(COUNTA(A:A), ROW(B1), "Add Entry")
For more HYPERLINK examples, and a sample file, go to the Hyperlinks page on my Contextures site.
Why would a pivot table show the wrong first name for an employee? My daughter ran into that problem recently, and refreshing the pivot table didn't fix the name. Most likely, someone accidentally typed over the original name in the pivot table.
There's no built-in way to get the original item caption back. You can manually type the original caption in the cell, or, if you don't know what the item was, use a caption reset macro from my site to fix it.
Use the original macro, for normal pivot tables, or the "Dual" macro, for normal or OLAP-based pivot tables (in the Data Model, or Power Pivot).
Here are a few Excel-related articles that you might find useful or interesting.
Excel Problems: According to this survey, we spend 30 minutes a day sorting out computer problems. The biggest headaches? Spreadsheets and presentations - even though 41% say they're spreadsheet experts! Does that sound like your co-workers? And if you want to avoid a few headaches, Bill Jelen (Mr. Excel) shares easy, but not obvious, solutions to Excel problems. (Level-All)
Name Manager: If you frequently use named ranges in Excel, you might like the helpful features in Jan Karel Pieterse's free Name Manager add-in. There's a recent update, and the add-in now works in 64-bit Excel too. (Level-Int/Adv)
Excel Humour: See what people said about Excel recently. Can you bond with your co-workers by complaining about Excel?
In the old days, my office had two bookcases full of computer reference books - mostly Microsoft Office topics. After lots of office cleaning, it's down to one shelf now, because Google is a lot faster than flipping through paper books!
Still on my shelf is this Excel Add-in book by Steve Dalton, which I bought with good intentions, but barely used. Our granddaughter found it fascinating though, on a long-ago visit, and pulled it off the bottom shelf, when my back was turned for a couple of seconds.
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2020/20200303ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: March 2, 2020 11:05 AM