Excel Hidden Formulas
August 27, 2019
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.
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.
In the cell where you want the hyperlink, create a HYPERLINK/CELL formula. For example:
=HYPERLINK("#" & CELL("address", AmtTotal!B4), AmtTotal!B4)
Go to my Contextures site, for more hyperlink and HYPERLINK function examples.
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.
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.
Here are the formula results, when triple spacing is selected. Every 3rd line is TRUE, so those lines are displayed.
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.
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)
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!
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.
Last updated: August 24, 2019 7:29 PM