Contextures

Contextures News

Show Messages in Excel Status Bar

Jan 26, 2016

Find interest rates with HLOOKUP, show helpful messages in the Status Bar, and more, in this week's Excel news. Visit my Excel website for many more tutorials and videos.

 -  Debra -  ddalgleish @ contextures.com

Get Interest Rate With HLOOKUP

If you've used Excel for a while, you've probably discovered the VLOOKUP function, and use it to pull data from a lookup table. There is a similar function -- HLOOKUP -- that looks for data across a row (Horizontal), instead of down a column (Vertical).

One way that HLOOKUP can help is finding the interest rate on a specific date. For example, set up a table (C2:F3) that has dates in the column headings. An HLOOKUP formula can look for the closest (earlier) date in that heading row, and return the interest rate from the row below. Here is the formula, where a date is entered in cell C5. TRUE is used in the last argument, to find an approximate match for the date.

=HLOOKUP(C5,C2:F3,2,TRUE)

You can learn more about the HLOOKUP function on my website, and download a file with other examples.

Status Bar Messages

For macros that take a long time to run, you can show progress messages in the Status Bar. Just add a few lines of code to your macro, and it will be easy to see what's happening.

It's really easy to do, and is much better than sitting at your desk, not sure if the macro is still running.

status bar message

Excel Articles

Here are a couple of Excel articles I read recently, that you might find useful.

Excel Conference -- Microsoft is hosting the Microsoft Data Insights Summit, March 22-23, 2016, in Bellevue, WA. The summit will focus on Excel and Power BI, and you can check out the session list to see what will be covered. The registration fee is $299 US, which is a great deal for a two-day conference!. (Level - Intermediate/Advanced)

Rounding Errors -- If you're using Excel to submit a proposal, be sure to read the specifications carefully, or you could be disqualified. One contractor lost a government bid due to incorrect rounding in Excel. I found it hard to believe, but there is a link to the government case file at the end of the article. (Level - All)

More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog. And for a bit of humour, read the weekly collection of Excel tweets,

Winter Fun

Fortunately, we weren't in the path of the big blizzard last weekend, but we do have a bit of snow. On Saturday, my granddaughter found this little blue sled at the park, and had fun sliding down the small hill a few times. It didn't work too well, but that added to the fun! Then, we headed home to warm up, and left the sled where we found it, for someone else to enjoy.

weekly photo

NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:

http://www.contextures.com/newsletter/excelnews2016/20160126ctx.html

_____________

See more recommended Excel products.

________________________

Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.

 

 

 

Search Contextures Sites

Excel Data Entry Popup List

 

 

 

 

Last updated: June 14, 2017 11:44 PM