Contextures

Contextures News 20200303

Excel Sheet Navigation - No Macros

March 3, 2020

Quick sheet navigation without macros, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.

Sheet Navigation

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.

link to first empty cell

How It Works

Cell B1 has a HYPERLINK formula, with 2 arguments:.

  • Friendly name - use the text that you want to show in the cell -- "Add Entry"
  • Link location - where the link should take you

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.

calculate row for next empty cell

Pivot Problem

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

fix pivot item captions

Excel Articles

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?

Also see: My Excel Products || Previous Issues

Advanced 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.

weekly photo

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.

Debra Dalgleish
dsd@ contextures.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

contextures newsletter info

 

Related Links

Hyperlinks

Pivot Item Macros

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: March 2, 2020 11:05 AM