Contextures

Contextures News

Quick Change for Excel Data

Apr 19, 2016

Quickly change settings to show different data, get the latest product updates, and more, in this week's Excel news. If there are topics that you'd like to see covered in future newsletters, please let me know.

 -  Debra -  ddalgleish @ contextures.com
     Visit my Excel website for many more tutorials and videos.

Choose Your Settings

Do you need a quick way to show different data in a worksheet? Perhaps you have different prices for Local and International shipping, or other options.

In Canada, we often have to switch between English and French, and someone asked me for help with the TEXT function. Here's how we show a date's weekday name:

  • For English users: =TEXT(B2,"ddd")
  • For French users: =TEXTE(B2,"jjj")

The function name changes automatically, but the date code doesn't, so we added a language selector at the top of the sheet. A lookup table stores the codes for both languages, so now it's simple to make the switch. You could add other columns to the lookup table, for prices, shipping rates, descriptions, or any other data you need.

You can download the sample file from my Excel Sample Files page, to see how it works. Also take a look at FN0029 - Show Text in Selected Language.

Your Feedback

Last week, I showed how to create a hyperlink is by dragging and dropping. Dennis emailed me, to ask how to get that to work on a protected sheet.

When you protect a sheet, you can allow users to select locked and/or unlocked cells.

  • If you allow selection of unlocked cells only, be sure to unlock the cell that the hyperlink goes to
  • If you allow selection of both locked and/or unlocked cells, the hyperlink will be able to go to the target cell, whether it is locked or unlocked

Product Update

There has been a major update to my Data Validation Multi Select Premium (DVMSP) kit. That kit lets you add a popup list for data entry, so it's easier to select from a long list of items.

In the new version (6.0), the Setup file has Ribbon commands, to guide you through the setup steps. There was also a minor change to the code in the popup list, to help it run a bit faster.

To check your version, look on the Overview sheet in the Setup file. To get the new version, use your original download link, or email me if you can't find your link.

Excel Articles

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

Data Visualization -- If you work with data in Excel, the Data USA site, from the M.I.T. Media Lab, will inspire you. It shows graphics, charts and written summaries of U.S. public data. There's a brief overview of the site in this NY Times article. (thanks to Jim W for the link) (Level - All)

Spreadsheet Apps -- For iPad users, MacWorld compares the spreadsheet apps -- Excel, Numbers and Google Sheets. The article tries to sell us on the beauty of Numbers' 3D charts, but ends with this verdict: "On the whole we'd go with Excel." (Level - All)

More Excel Articles -- Visit my Excel website for many more tutorials and videos.

The Dalgleish Car

Last week was a tough one - my husband's father passed away, at the age of 93. Of course, there were tears at his memorial, but smiles and laughter too, as we shared stories of his long and productive life. One of my husband's treasures is this car that his dad made, based on a picture of the 1908 Dalgliesh-Guilane.

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:

www.contextures.com/newsletter/excelnews2016/20160419ctx.html

Recommended Excel Products

  • FastExcel: Watch the short video on my website to see how these tools help you manage your Excel files, identify calculation jams, and remove or reduce the problems. If you work with large, calculation-heavy Excel files, FastExcel is a great investment.

________________________

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:48 PM