Tag Archives: workaround

A little bit of history – lost … and found

Excel has been around for decades, so it isn’t surprising that there are many features tucked away under the hood. What is surprising is when a useful feature is lost and only careful archeology can bring it back to life.

Excel 4.0 had a rudimentary macro language, mostly using excel formula approaches to building functionality. This was replaced by the VBA programming language. But there are still useful little items tucked away in this early language that haven’t been replaced.

One of these is Get.Cell.

Get.Cell had a boatload of switches that allowed the user to pull information about the cell formatting and contents and most of these have been replaced by the Cell and Type functions in Excel.

But one piece of information that Cell and Type can’t tell you is whether your cell or cells contain formulas vs values and sometimes this is a very handy thing to know at a glance. For example, if you build a spreadsheet using formulas to estimate amounts; but then start to drop in values as more concrete information becomes available.

In this situation I like to format cells containing formulas differently from the cells containing values, so that I can see at a glance where my estimates are. Its’ handy to have the formatting change automatically, so I don’t have to remember what my rules are weeks or months later.

This is where Get.Cell shines. The syntax I’m going to use is

=GET.CELL(48,A1) – where A1 is the cell I’m going to reference.

The trick here is that Get.Cell is NOT entered in a cell, but instead as a named formula. After creating the named formula, I can reference it while applying conditional formatting. In this way, when the type of content in the cell changes the conditional formatting automatically updates.

Using a named formula in Conditional Formatting - dialog box
Using a named formula in Conditional Formatting

Where does one find information about the Get.Cell function? Not from Microsoft or at least not easily from Microsoft.

Try this post https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html   to see the possible switches for Get.Cell

 

 

Outlook – Retrieve Dismissed Reminders

Some days, your biggest enemy is yourself.  Have you ever hit that “Dismiss All” button, or accidentally dismissed a reminder you wanted to keep?

Reminder Dialog box with highlighted Dismiss All button
Oops! I hit the Dismiss All button!

Here’s how to find that reminder, so that you can reset it.

Use the search, but instead of searching by topic, type in modified:<date>

Highlighted Search bar
Use the Search bar in the calendar view
Close up of search entry
Close up of search entry

Use the date when you accidentally dismissed those reminders (causing the modification). Your view will automatically switch into the Search Tools view. If you have multiple calendars you want to search (as I do) make sure the All Calendar Items button is pressed.

Closeup of the Search Tools view
Closeup of the Search Tools view

It will show you all the reminders you’ve modified.

In fact, you don’t need to be too precise about dates. Here’s an example, where only the name of the month was typed in.

Still works!

Search by the month name if you don't know what date.
Search by the month name if you don’t know what date.

You can then reopen the item and reset the reminder.

Fixing an annoyance in Outlook 2010

The scenario:

You like to flag your mail for Follow Up on a specific day. But Outlook always defaults to setting the reminder at the end of the day. Can this be changed to the beginning of the day?

It makes sense that if you want a reminder set for Today, that the default time for that flag is set to one hour before the end of your work day (as defined in your Calendar settings).

It makes sense that flagging a reminder for one of the pre-defined future dates (Tomorrow, This Week or Next Week) uses the start of your work day as the default time.

It makes NO SENSE that flagging a Custom date reminder reverts the default time to one hour before the end of the work day.

To change this default to the start of your work day.

Finding the Quick Click menu
Finding the Quick Click menu

Click on the drop-down arrow on the Follow Up button on the Home Ribbon.

Select Quick Click

The Quick Click dialog box
The Quick Click dialog

The Set Quick Click dialog appears. Choose Tomorrow as the default. Click the OK button.

Done. Future custom reminder times will now default to the start of the work day – not the end.