[Libs-Or] Tech-Talk: EXCEL & SHEETS - Create Custom Drop-Down Lists

Tue Oct 23 13:47:25 PDT 2018

Greetings and welcome to this week’s edition of Tech-Talk!

Remember, you can access previous issues through the Tech-Talk archive – see below the Video Tutorial for more information.

[http://files.constantcontact.com/ee1208b4001/34916cb2-412f-43fa-8294-1f7ecb0e9a3d.png]

 This Week's Topic: MS EXCEL and Google Sheets

1. VIDEO ... Create Custom Drop-Down Lists
2. ARTICLE ... Create Custom Drop-Down Lists
3. COMMUNICATING ... Sentence completion when interviewing

[video]<http://r20.rs6.net/tn.jsp?f=001u3N8LPHNMxwIS4ROJMYCHmvSskpYx-OkBSQjYPsh7R1C48vdqsWHs3fT3LBJ8OlFHMjj1ime_gX0rwsprrCjw_4VS7-5Nj_aNvZ2s8aXCrDBYp7iQx7Sw6ddPRHyDTiwTwvrP_4_sSw4QIpWl_cHuTtXH0_JOqI2mR_pjds2uXHfQAtxoprstfVe8yeGnjZw&c=bAf-FwNz8zM8vVTG6Hh70EweiXyaOquzCW1hT_2CKoc03Pl-eaaOmA==&ch=7X_Aef0aGLZ60ALuX550OX_vUCePlkt7p_fdtNHKTFonLa63QFWZtg==>

Photo by Sneha Chekuri<http://r20.rs6.net/tn.jsp?f=001u3N8LPHNMxwIS4ROJMYCHmvSskpYx-OkBSQjYPsh7R1C48vdqsWHs3fT3LBJ8OlFLyyTM-V1mXruqxrJfhSLYQAPPz8wvmvwfRArLhb89d13QGnywbPK9oCKKO_zrFPvZ_usswS3Qtr8VcRvOHjpm5tj-NL9Xx-I5tOvbiT5aKR6XCpmQ9AU2wbeiQW0xIyfB4VespwhQI41Od5tHgtAs3lgQLL9albt0xpc_u0pyNQZzAvnYltPD1YsJ4chpyMhuyqxQgmqQkdI-nYVC0BpHw==&c=bAf-FwNz8zM8vVTG6Hh70EweiXyaOquzCW1hT_2CKoc03Pl-eaaOmA==&ch=7X_Aef0aGLZ60ALuX550OX_vUCePlkt7p_fdtNHKTFonLa63QFWZtg==> on Unsplash<http://r20.rs6.net/tn.jsp?f=001u3N8LPHNMxwIS4ROJMYCHmvSskpYx-OkBSQjYPsh7R1C48vdqsWHs3fT3LBJ8OlF5YWg3Ph5J9xKrNDYliix9ziFkGFHt7DQKA2TMTFyGP42KcIMgsUKEMXZo8RsCY9xTI6h-j184LvpYngpy_fXr7VjgSyrq3jv2bUJTI1K5_0TduLM8UUHQ56uxFSXCmbVhazJp5Z7WDfANNUcYz__JX9mbcnNhfd6cqEuZdwPBFrV2nNoOYVe1GJGpDCLIGFK_YzUT5zsVre7DZPZb7L5UxGSZdM-osO2&c=bAf-FwNz8zM8vVTG6Hh70EweiXyaOquzCW1hT_2CKoc03Pl-eaaOmA==&ch=7X_Aef0aGLZ60ALuX550OX_vUCePlkt7p_fdtNHKTFonLa63QFWZtg==>

Brought To You By State Library of Oregon

[State of Oregon]

Visit Tech-Talk.com Archives<http://r20.rs6.net/tn.jsp?f=001u3N8LPHNMxwIS4ROJMYCHmvSskpYx-OkBSQjYPsh7R1C48vdqsWHsw-F3_Np3uUOCIwu7hQRnBOjPO3t-JBmYZJ1PnQSfD207EWIbiY-eHGEOLggyZLaxUbmRx9NeMIeboPMCrtAZ4v1_Sr0RNsInXy-3xqDQfqs&c=bAf-FwNz8zM8vVTG6Hh70EweiXyaOquzCW1hT_2CKoc03Pl-eaaOmA==&ch=7X_Aef0aGLZ60ALuX550OX_vUCePlkt7p_fdtNHKTFonLa63QFWZtg==>

Username: ORLIBTECH
Password: ORLIBTECH

Questions about Tech-Talk?
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us>

MS EXCEL & G-SHEETS - Create Custom Drop-Down Lists
Advanced

The other day I was helping a colleague update some administrative processes. She liked to use Excel to track her organization's income and expenses for the events they sponsored. However, it became apparent that there were some naming inconsistencies, so the totals weren't accurate.

For example, for one event she used the term "space rental" and for another, it was "room use." We needed to standardize the income and expense names so she could compare apples to apples going forward.

When you're working with accounting, inventory or a mailing list (as examples), you want all the related items for one topic to be the same. That means every item must have a consistent name.

That's where drop-down lists come in handy.

You can set up a form so that the choices are ready in a pre-determined list. Then you can use this drop-down going forward and everything will be consistent.

TIP: This can be done in both Excel and Google Sheets.

[create custom drop-down list]

Administrative Use

You can create spreadsheets that would be typically used internally like:

·     Invoices
·     Expense worksheets
·     Inventory lists
·     Mailing Lists

Customer / Patron / Member Use

Or there may be Excel forms you use for people external to your organization which you want to make easier to fill out accurately and consistently, such as:

·     Program sign-ups
·     Class registrations
·     Product order forms

You can use drop-down lists in Excel or Sheets for anything you put into a spreadsheet where you would like to have people make selections.

Create a Custom Drop-Down List in Excel

Step 1: Create Your List

To create your own custom list, in a nutshell, you are putting your choices in one worksheet… and then calling that list into the cells of another worksheet to create your drop-down menu. However, both worksheets will be in the same workbook.

·     First, set up your spreadsheet so the form is ready to have the list inserted into the appropriate cell. For instance in the example below, put in the title, the column heads, the total cost line…everything except the list of categories and dollar data.

[form]

[create your list]

·     Then, go to a new worksheet (a tab at the bottom) and type the list of items for your drop-down menu in this new worksheet. They need to be in one column, without any cells skipped. Make sure they are in the order you would like them to appear in the drop-down list.

·     Next, you need to "Name" your list. Start by highlighting the group of cells, and then in the Name Box in the upper left (to the left of the formula bar), type in the Name of this particular drop-down list.

Step 2: Adding Your List to the Spreadsheet

·     Go back to the worksheet where you want the list to appear; click in first cell.

·     On the Data Tab, in the Data Tools section, click on the Data Validation option.

·     In the Settings Tab, under Allow, select List.

·     In the Sources section box, type an equal sign (=) followed by the Name of the List you created previously (Don't click OK yet ... there's more).

[data validation]

·     On the Input Message Tab, make sure the "Show input message when cell is selected" box is checked.

·     In the Title section, type in the Name that will show when someone clicks on the cell of your drop-down list.

·     Below this in the Input Message box, type in any instructions you would like displayed for the end-user. (Don't click OK yet ...)

[input message]

·     Finally, if you want a message to appear if someone tries to type a new option in the cell instead of picking from the drop-down choices, go to the Error Alert Tab and in the Title section, type the message heading that will be displayed if they do this.

·     In the Error Message, add your specific instructions.

·     Click OK to leave the Validation window.

[error alert]

Next, take a look at your drop-down list to see if it displays correctly – but don't select anything from the list.

CAUTION: When you click the drop-down arrow, if the list choices are cut off, just click on the column divider line and drag to make it wider so your titles are fully visible.

To test your error message, try typing something into the cell and see what happens.

If you want to make any changes, click the cell again for the drop-down list and to the Data tab and choose Data Validation option. Go to any of the tabs where you want to make changes. Be sure to click OK to save your new changes.

Step 3: Apply Your Custom Drop-Down List to Additional Cells

If everything looks good and you're ready to apply this to a whole range of cells, click the bottom right corner of the cell with the drop-down list and drag it down the column of your form for all cells you want to be included.

TIP: If you want to Hide the worksheet that contains all of your lists so users cannot make changes, right-click the sheet tab and select Hide. If you want to view the worksheet at a later time, just right-click on the sheet to the right or left of the hidden one and select Unhide.

To Delete a Drop-Down List From Your Excel Worksheet:

·     Click on the cell with the list.

·     Go the Data tab, and then in the Data Tools section, click Data Validation, then Data Validation again.

·     In the Settings tab, click the Clear All button in the bottom left, and then OK.

·     The inputted text will remain, but the drop-down list will no longer be a choice.

Create Simple Drop-Down List in Google Sheets

Google Sheets makes it even easier to create drop-down lists.

·     In your spreadsheet in Google Sheets, select the cell or range of cells where you would like to display the drop-down list.

[google sheets]

·     Go to Data, then from the drop-down list choose Data validation.

·     In the window, under Criteria, it's easier to select List of items. Then enter each choice, separated by commas (do not add spaces). If the list of categories is already in your spreadsheet you can use the List from a range option.

·     The default setting is set to show a downward arrow in the cell with the drop-down list. To turn this off, un-check the Show drop-down list in cell box. We have left this option turn on to give the user a cue that there is a drop-down choice available.

·     Under On invalid data, check the option to Reject input if you only want the ability for people to choose from the list. Select Show warning if otherwise.

·     Click Save.

[create a drop-down list]

To Make Changes or Delete Your Google Sheet Drop-Down List

·     Open the Sheet and select the cell or cells you'd like to modify.

·     Go to Data and select Data validation.

·     To change items in your list, edit them in Criteria.

·     Or, to delete a list, click the Remove validation button.

·     Click Save.

TIP: If you change the contents of the range you've selected, the changes will be made in the list automatically.

[http://files.constantcontact.com/ee1208b4001/f8f1ecc8-4884-4c21-8ed0-cf647e6db944.png]

Communication: Interviewing
Find out what someone really thinks

Let's imagine you're in a position to bring a new person onto your team. You have several candidates and you want to pick the right one.

Sure, you'll conduct an interview where you share what is expected and ask pertinent questions. You'll do your best to assess the situation based on the answers you get and the manner in which individuals handle themselves.

One Technique We Use

Whether I have only one person I'm considering, or several, I always end the interview session with a Sentence Completion exercise. I hand them a blank sheet of paper and ask them to write the phrases:

"One of the reasons I'd like this job is..." and
"One of the concerns I have with this job is..."

I tell them the rules are simple:
·     Complete the sentence 10 times
·     Do it as quickly as possible, whatever comes to your mind first.
·     The answers can be duplicate or contradictory.
·     They can be serious or silly.
·     They must complete all ten -- even if they don't have more answers to give.

One of the reasons I'd like this job is ...
1.  ________________________________________________
2.  ________________________________________________
3.  ________________________________________________
4.  ________________________________________________
5.  ________________________________________________
6.  ________________________________________________
7.  ________________________________________________
8.  ________________________________________________
9.  ________________________________________________
10.  ________________________________________________

I guarantee that you'll discover very quickly who has the right attitude, confidence and approach to the job or task you're offering.

[http://files.constantcontact.com/ee1208b4001/b13a7a6a-7445-4d8e-b741-4b475385ef5b.png]<http://r20.rs6.net/tn.jsp?f=001u3N8LPHNMxwIS4ROJMYCHmvSskpYx-OkBSQjYPsh7R1C48vdqsWHs5lbinkbctdeN1cl-pGorsBYlSdROAcHTqOqwqaqRW1DFYI8OFhPoUWqQwZ3N-efMjTe0O2Tx06CY1hWitQ2HpburOlZv_-V7_KbMivZS9mu&c=bAf-FwNz8zM8vVTG6Hh70EweiXyaOquzCW1hT_2CKoc03Pl-eaaOmA==&ch=7X_Aef0aGLZ60ALuX550OX_vUCePlkt7p_fdtNHKTFonLa63QFWZtg==>

[Ask a question]<http://r20.rs6.net/tn.jsp?f=001u3N8LPHNMxwIS4ROJMYCHmvSskpYx-OkBSQjYPsh7R1C48vdqsWHs4OzptpD7XAFtQ3we5KvKp159NTsI4qSoIX8Jvhr3Ku3fpOLEdudItbMj53sTRHdadlwt0mHEMHbOYar6fx__lRW1Qw37L0IXLP19qFAdhpHP3Y9QkvpkEk=&c=bAf-FwNz8zM8vVTG6Hh70EweiXyaOquzCW1hT_2CKoc03Pl-eaaOmA==&ch=7X_Aef0aGLZ60ALuX550OX_vUCePlkt7p_fdtNHKTFonLa63QFWZtg==>

Copyright 1996-2018 Shared Results International. Published weekly. Do not forward this issue of Tech-Talk.com without prior permission from Shared Results International. Distribution is limited by contract. Forwarding it to unauthorized recipients constitutes copyright infringement.
For information on site licenses or how to include additional recipients, email darci.hanning at state.or.us<mailto:darci.hanning at state.or.us>

The Tech-Talk e-newsletter for Oregon library staff is distributed weekly via the Libs-OR and TechTalk mailing lists. Please do not forward. Know someone who might be interested in receiving a copy but they’re not on Libs-OR? Have them contact darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> or visit http://listsmart.osl.state.or.us/mailman/listinfo/techtalk to subscribe to just the Tech-Talk newsletter!

This paid subscription service is supported in whole by the Institute of Museum and Library Services (IMLS) through the Library Services and Technology Act (LSTA), administered by the State Library of Oregon.

Cheers,
Darci Hanning, MLIS
Public Library Consultant / CE Coordinator
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> | 503-378-2527| www.oregon.gov/library<https://www.oregon.gov/library>
Follow us: Facebook<http://fb.me/StateLibraryOR> | Twitter<https://twitter.com/StateLibraryOR> | Tumblr<http://www.statelibraryor.tumblr.com/> | Pinterest<https://pinterest.com/statelibraryor/>

[cid:image005.png at 01D31C27.4B9FEA30]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20181023/7f94c585/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15548 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20181023/7f94c585/attachment.png>