Contextures

Contextures News 20190827

Excel Hidden Formulas

August 27, 2019

Hidden formulas, adjustable hyperlinks, 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.

You'll get my next newsletter in 2 weeks -- we're on the summer schedule now.

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

Adjustable Hyperlinks

If you insert a hyperlink in a cell, the hyperlink doesn't change if the linked cell moves. For example, if you link to a heading in cell B10, and a few rows are inserted above, the hyperlink still takes you to B10, not to the heading cell's new location.

Here are 2 ways to make a hyperlink that adjusts automatically, when the linked cell moves.

1) Link to a named cell, instead of a cell address:
  • Select the cell where you want to create a hyperlink, press Ctrl+K
  • At the left of the Insert Hyperlink dialog box, click Place in This Document
  • Under Defined Names, click on the name you want to link to
  • (optional) At the top, change the text to display in the cell, then click OK

QAT command for specific workbook only

2) Use a formula

In the cell where you want the hyperlink, create a HYPERLINK/CELL formula. For example:

=HYPERLINK("#" & CELL("address", AmtTotal!B4), AmtTotal!B4)

  • Because the link is within the same workbook, it has to start with a pound sign.
  • The CELL function creates a text reference to the cell
  • The text from the linked cell is used as the display text

Go to my Contextures site, for more hyperlink and HYPERLINK function examples.

Hidden Formulas

In my Excel weekly planner template, you can make changes on the Setup sheet, to customize the planner layout. For example, choose a date format, change section headings, and adjust the line spacing, simply by selecting from drop down lists.

QAT command for specific workbook only

The line spacing is controlled by conditional formatting, which uses hidden formulas. Column L has formulas that number the lines, and column K has a MOD function, to check if each row should have a line, based on the Line Spacing that was selected.

=IFERROR(MOD(L12,ToDoLinesID)=0,FALSE)

Here are the formula results, when triple spacing is selected. Every 3rd line is TRUE, so those lines are displayed.

QAT command for specific workbook only

Get the sample file, and see the detailed instructions on my Contextures website. There's a video too, that shows how to customize the planner, and how the hidden formulas work.

Excel Articles

Here are a couple of Excel articles that you might find useful or interesting.

Formulas: Dick Kusleika used Excel formulas to analyze his golf game this summer. Download his sample file, to see how it works - you could adapt the formulas for work-related projects. And get my workbook to keep track of golf scores. (Level - Int)

Functions: On the Financial Management blog, Liam Bastick takes a deep dive into Excel's MOD function (Level - Int/Adv)

Also see: My Excel Products || Excel Events || Previous Issues || Excel Twitter

Tiny Laptop

My granddaughter set up a doll village at our house, and one doll has a tiny laptop, with pink things on its screen. I'd like to find a tiny picture of an Excel workbook, and ask if she'd like to paste it over that pink stuff. Then that laptop would really be useful!

And that doll must be a programmer, with her giant bottle of Pepsi to keep her caffeinated for an all-night coding session!

weekly photo

NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190827ctx.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.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: August 24, 2019 7:29 PM