[techtalk] EXCEL / G-Sheets - Link Data to Other Worksheets or Workbooks

Tue May 19 15:38:44 PDT 2020

Greetings and welcome to this week’s issue of Tech-Talk!
e how to connect data in different Worksheets
[http://r20.rs6.net/on.jsp?ca=7f634835-e19f-433c-8bb4-e705d4653902&a=1100778316270&c=980e0ed0-0713-11e6-a7b7-d4ae528ed502&ch=981cb4d0-0713-11e6-a7b7-d4ae528ed502]

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

Announcements

Extended through JUNE: If your library, school or academic institution doesn't already have an unlimited Tech-Talk license for your STUDENTS, FACULTY and PATRONS, you can get one for free during this nationwide shutdown. Apply here<http://r20.rs6.net/tn.jsp?f=001oVUPqRwvmhHyJe1NramVOyFsb1nQaRLEH51PlaHXGavnPY7BhzC9Q-HjwJ1zI0nRTnAeW4qRnG95yDEy_R5ju_hKphgn2NM9lFH38JbVfoamPw6lgGdPlSP38UNEkLAmQoh6uw28W5Zc2_L_y5pLj8Pl1pE1XazXb-qsMgJXgYQ=&c=WdOyO5ZZVzWc969gGW3ub9Y0nSMCS0iv-ZGAuaJyZ457DM2U_pCAtQ==&ch=mh163X76rV0UZZ1kJoLjswWeoAyw_64dR2owLiae5w7YmfdQY0tS8A==>.

UPCOMING WEBINARS

·     May 20: COMMUNICATIONS: Tips for Dealing with Difficult People
·     May 27: VIDEO: In Windows 10, Create Videos With Photos App
·     June 3: DESKTOP: Organizing Thoughts & Naming Conventions
·     June 10: INTERNET: Clever Ways to Search
·     June 17: LEADERSHIP & TEAMS: Six Questions You Must Discuss
·     June 24: WORD: More Tips and Tricks

Register Here<http://r20.rs6.net/tn.jsp?f=001oVUPqRwvmhHyJe1NramVOyFsb1nQaRLEH51PlaHXGavnPY7BhzC9Q8g9ZNsOXQnD-hLRqh2q0nPlLJdwYXEVpdKHqz2svQoagvRjykhecPLLiQRky2CeDG3_UxZ03Wox24Uhp51t8lx0PdzKNwaIH1Xhg0sZdDRY&c=WdOyO5ZZVzWc969gGW3ub9Y0nSMCS0iv-ZGAuaJyZ457DM2U_pCAtQ==&ch=mh163X76rV0UZZ1kJoLjswWeoAyw_64dR2owLiae5w7YmfdQY0tS8A==>

Watch the Recordings Here<http://r20.rs6.net/tn.jsp?f=001oVUPqRwvmhHyJe1NramVOyFsb1nQaRLEH51PlaHXGavnPY7BhzC9Q19heqfCNIKa5j46kC9IwI1B9SM2zZHX0vCTzlyX7yH5pGetPn4WriNSse5lmCnQPs1QwwwcFq4thp67HLnkTmcs1he_U_1NTpRYzirRIB4QPQtrJr1V_8k=&c=WdOyO5ZZVzWc969gGW3ub9Y0nSMCS0iv-ZGAuaJyZ457DM2U_pCAtQ==&ch=mh163X76rV0UZZ1kJoLjswWeoAyw_64dR2owLiae5w7YmfdQY0tS8A==>

 This Week's Topic: EXCEL / Google Sheets

1. ARTICLE & VIDEO ... Link Data to Other Worksheets or Workbooks
2. COMMUNICATING ... End your "no" with an upbeat
3. LEADERSHIP ... How often do you give praise at least once every 7 days?

[https://files.constantcontact.com/ee1208b4001/109721c2-d4b9-4eaf-8b74-a7c1eaf369ff.png]<http://r20.rs6.net/tn.jsp?f=001oVUPqRwvmhHyJe1NramVOyFsb1nQaRLEH51PlaHXGavnPY7BhzC9Q004hmlYauA_KImLaPlzKb58JtHbNxGcsNrZwqqY_BoZ5qfhr1x9sUJh-GaUNX4Exb6sldvoaX37sMPcaY-CAfuywWLmEqbXWm3y29B5EbXVRjjPzWdYT12rQGagfJok3hes60j7fdVxIUa426jYH_prjDtSxTsAEg==&c=WdOyO5ZZVzWc969gGW3ub9Y0nSMCS0iv-ZGAuaJyZ457DM2U_pCAtQ==&ch=mh163X76rV0UZZ1kJoLjswWeoAyw_64dR2owLiae5w7YmfdQY0tS8A==>

Photo by Sheri Hooley<http://r20.rs6.net/tn.jsp?f=001oVUPqRwvmhHyJe1NramVOyFsb1nQaRLEH51PlaHXGavnPY7BhzC9Q004hmlYauA_eIvz518GgB1PWVEtOTw09nqeoAHSlIpB0jz_jelzVS0cDCTAv61tZL4NmFaoVl1IK2q2An6A1Hp8MaFuHjRarQ_JvkhRi2vi0kTYDv-V_Xlnu_4YRjigbNuzl8VhBXbkgO-iV9wYlSo7cfTKplfyM23REGhEcH-gw2RpSy2W4n9pX59_HVeP9GZmvswe7cAGfjfhcP5h-Tc=&c=WdOyO5ZZVzWc969gGW3ub9Y0nSMCS0iv-ZGAuaJyZ457DM2U_pCAtQ==&ch=mh163X76rV0UZZ1kJoLjswWeoAyw_64dR2owLiae5w7YmfdQY0tS8A==> on Unsplash<http://r20.rs6.net/tn.jsp?f=001oVUPqRwvmhHyJe1NramVOyFsb1nQaRLEH51PlaHXGavnPY7BhzC9Q004hmlYauA_IW33WpAaGoZj7lBlRroXoXPpjlgsNRTTWRk0-dQPs4YXEImywteCWfYG6lYXEqiFRuqYOox00XwN2aZBKftjoTMf5N3o7DiWdCbcYJpgd9-vmX5z-znOV5tpKUNzQNS8hnlcWuiqZYpv3I9-k2inowAjFE69sT-jVY7gDQ_4UByqsU65xh_zj0CEMTi9lrzbwUkg0pvV4bP8iXgOHQeDx4Gxww7BTUaO&c=WdOyO5ZZVzWc969gGW3ub9Y0nSMCS0iv-ZGAuaJyZ457DM2U_pCAtQ==&ch=mh163X76rV0UZZ1kJoLjswWeoAyw_64dR2owLiae5w7YmfdQY0tS8A==>

Brought To You By State Library of Oregon

[State of Oregon]

Visit Tech-Talk.com Database<http://r20.rs6.net/tn.jsp?f=001oVUPqRwvmhHyJe1NramVOyFsb1nQaRLEH51PlaHXGavnPY7BhzC9Q3TVUUCnwcD21JrkjZaiwrLe0s4obxHVC7Y_shbWe4cQ75pCAi2ACNXj5rjyaZH5ofnjecKM3khcFF28qwMaG5di7M-a-gzkaemURXRuIzpy&c=WdOyO5ZZVzWc969gGW3ub9Y0nSMCS0iv-ZGAuaJyZ457DM2U_pCAtQ==&ch=mh163X76rV0UZZ1kJoLjswWeoAyw_64dR2owLiae5w7YmfdQY0tS8A==>

Username: ORLIBTECH
Password: ORLIBTECH

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

EXCEL – Link Data to Other Worksheets or Workbooks
Advanced

We recently presented a webinar on Advanced Techniques in Excel<http://r20.rs6.net/tn.jsp?f=001oVUPqRwvmhHyJe1NramVOyFsb1nQaRLEH51PlaHXGavnPY7BhzC9Q004hmlYauA_jUVqLtAf4FOi3lrvASfboFxdzxf53E8Ox32z_UlJVcyZRcKi9-LOY7y4MKsOWtLO2EbSEVx27UY-bCJ52tkj0VtzBb6iJs9wKK0DYG-XMUulxykpAqEGVImkL06JibnvDR8T7mRbitB1aHrwBJ-I4w==&c=WdOyO5ZZVzWc969gGW3ub9Y0nSMCS0iv-ZGAuaJyZ457DM2U_pCAtQ==&ch=mh163X76rV0UZZ1kJoLjswWeoAyw_64dR2owLiae5w7YmfdQY0tS8A==>. One participant asked us to explain how to link data from one Excel Sheet to another. We love it when we can take a question from a Tech-Talk reader and create a tip that may be helpful for others!

Quick Definition: An Excel Workbook is the entire named file. Within the Workbook are single page Worksheets (or tabs at the bottom). A Workbook can contain one or more Worksheets.

Example (4 Worksheets within the one Workbook)

[worksheets]

Linking Worksheets in the Same Workbook

Worksheets in an Excel Workbook may contain data that relates to each other. For example, you may have a budget Workbook that has 13 different Worksheets in it (one summary tab and 12 tabs representing each month of the year).

Wouldn't it save time if when you entered data in one of the months, that total would automatically appear in your summary Worksheet? It can!

[worksheet tabs]

Linking Worksheets in Different Workbooks

Or, you may have different Workbooks (separate Excel files) that have related data. In this case, you could have volunteers log their time in a "Volunteer Hours" Workbook. But for your tracking and reporting purposes, you pull those numbers into a different document. You can actually link the totals from one Workbook to another automatically. If you change the data in one Workbook, it updates it in the other.

CAUTION: One important detail to remember. You will want to save both files in a location that is static, preferably the same folder. You can't move either file around, email a file to someone, etc. -- as it will break the connection (formula) and you may lose data. So this solution is possible if care is used.

Let's Set Up the Example Situation

In the technical directions below, it will be helpful to understand the situation we're using so that you won't get lost as you move back and forth between "source" and "destination" spreadsheets ... and navigating within one or more workbooks.

We've created an imaginary library that holds five "income generating" events every year (e.g. Silent Auction, Book Fair, etc.). This organization wants to have one spreadsheet that shows, year over year, how their net income compares after they've taken out all the expenses. They want to automate the process so that entries in the individual event sheets, update the master one.

So let's take a look at how to link data in Excel files.

How to Link Data from One Worksheet to Another in the SAME Workbook

For clarification, we will refer to a "Source Worksheet" – which is where the original data resides. And a "Destination Worksheet" which contains the link (formula) where data is updated automatically.

In our first example we have one Workbook for "Event Income Tracking" that has several Worksheets in it ... one for each individual event. We want the net income of each event (from the source Worksheets) to be automatically updated in the summary tab (the destination Worksheet). There are two methods you could use.

[copy and paste]

Option #1: Copy and Paste

·     With the master Workbook open (the one with all the spreadsheets), go to the source Worksheet (the input data), highlight the cell with the data that you want in the other spreadsheet, right-click and choose Copy.
·     Next, go to the destination Worksheet tab (summary sheet), put your cursor in the cell where you want the data to show and automatically update when the underlying source numbers change ... but don't paste yet!
·     From the Home tab, click on the drop-down arrow in the Paste button, and from Other Paste Options choose Paste Link.
·     Or, right-click in the cell on the destination worksheet and choose Paste Link from the Paste Options.

[paste link]

·     Hit the Esc key to move out of that cell.

Now anytime the content from the cell in the source Worksheet changes, it will update in the destination tab.

Option #2: Enter the Formula

Alternately, you can enter the formula manually.

·     In this case, go to the destination Worksheet and put your cursor in the cell where you want the data to pull in from your source tab.
·     Enter an equal sign (=)

[enter =]

·     Then go to the source tab, click on the cell that contains the data and press the Enter key on your keyboard.

[https://files.constantcontact.com/ee1208b4001/7a2033d4-5677-4d15-808c-62aadbcfe120.png]

·     The data will now appear in the destination Worksheet.

Using either method, now anytime the content from the cell in the source Worksheet changes, it will update in the destination tab.

Link Data from One Workbook to a DIFFERENT Workbook

Creating the links between the spreadsheets in different workbooks is the same as above: Option # 2-- Enter the Formula. (NOTE: Option #1: Copy and Paste does not work between different workbooks.)

However, there are important considerations to keep in mind...

·     When creating the connection, BOTH Workbooks need to be OPEN.

·     And if you change data in the source Worksheet when both Excel files are open, it will automatically update. But if both Workbooks are not open, the next time you open the destination file that contains the references, you will get a message that looks like this: Click Update to refresh the file.

[update file]

·     Again, if you move one or both of the Excel Workbooks (the destination or source files) to a new location, the file path is essentially broken. Keeping both documents in the same folder is recommended.

Linking in Google Sheets

You can link Workbooks and Sheets in Google Sheets, however, the process is a bit different. You have to put in formulas, you can't copy and paste.

In this example, we have a source sheet (called Silent Auction) and destination sheet (referred to as Event Income Summary).

[Event Income Summary]

To connect one Worksheet to another in the same Workbook:

·     In the source Worksheet note the cell reference that you want to carry over to the destination sheet. In this example it is D20.
·     Go to the destination sheet and click in the cell where you want the linked data to appear.
·     Type: 1) an equal sign (=); 2) followed by the sheet name; 3) then an exclamation point (!) that serves as a separator; 4) followed by the cell name.

In our source file it looks like this:

=SilentAuction!D20

[event summary]

·     Then press Enter.

IMPORTANT: If your "tab name" has a space in it, then you need to enclose that name in single quotes. So if the tab name in this example was Silent Auction, the formula would be:

='Silent Auction'!D20

Link Data in Different Google Sheets Files

It's a bit more complicated to link from one Sheet in Google to a different Sheet file. It involves adding the file name (the URL path) of the sheet in the formula (because the document is online).

·     Open the source Google Sheet and copy the file name -- the whole URL at the top of the window.

[source file name]

·     With that copied, go to your destination Google Sheet.
·     The formula will be a series of information that point to the cell reference staring with: 1) =importrange; 2) followed by the file name of the Sheet; 3) the tab (sheet name); and 4) the cell reference.
·     For our example, it would look like this (note where parenthesis and comma are added):
=importrange("https://docs.google.com/spreadsheets/..." , "SilentAuction!D20")

[file path]

[allow access]

·     Then click Enter.

You may get an error message that prompts you to allow access to the file. Click the Allow access blue button.

[https://files.constantcontact.com/ee1208b4001/41d9a61c-7e5a-4de3-b0a4-4021dd05cea3.png]

Communications: Talking & Writing
Say "No" with a positive spin

You invite someone to join you in an early morning Pilates exercise. Your friend says, "Thank you for thinking of me, but I will decline."

Notice how you feel. Rather rejected, right?

Now imagine she had said the same thing but in a different order.

"I will decline, but thank you for thinking of me."

Don't you feel better hearing the same message but with the positive part at the end?

Whatever is said last, is the message that lingers. So if you want people to feel better when you're delivering a "no" ... or an unpleasant message ... consider ending on an upbeat.

[https://files.constantcontact.com/ee1208b4001/da1c6ee9-ba35-4af5-9c80-8e1b5781189e.png]

Leadership
How often do you give praise?

The Story of MO (Win as the Executive, Riptorn as the Manager / Supervisor, and Val as the Employee) has been published in over 50 different industry journals.

The topic .... 'organizational indifference.'

This is what happens when the "VALs" in the company aren't being listened to, aren't being included in the big picture, aren't being respected for adding value to the organization.

True Story. A month after the articles were published we received a phone call from an engineer in California. He said...

“I saw your topic in 'Facilities Engineer' and I really liked it.
But there was one thing you said that
would have to be changed for our environment.

You had a quiz, with the question,
'How often do you receive praise at least once every seven days?'

In our world, you would want to ask,
'How often do you receive praise at least once every seven years.'

When we tell the story in person, people tend to laugh. Why? Because it is so sad, yet so real.

You don't have to be a Riptorn (manager) to give praise. You can take the lead in your group to make sure that everyone's ideas are heard and considered. You can put forth the praise (privately and in a team setting).

Ask Yourself…

1. How well do I observe when others do great things, have creative ideas?

2. How often do I give praise? What can I do to make this a habit?

[http://files.constantcontact.com/ee1208b4001/b13a7a6a-7445-4d8e-b741-4b475385ef5b.png]<http://r20.rs6.net/tn.jsp?f=001oVUPqRwvmhHyJe1NramVOyFsb1nQaRLEH51PlaHXGavnPY7BhzC9Q8D6Y-gK_Luu2mLqfaSJ8gg_MDqlaruu1yNsTcn63K1h7_eFTT0kYBCnYiSYHv-OGbJj2GeKKCfmJCOYjx3aeAH3U4iNpHHkSoR6Vfh7clM6&c=WdOyO5ZZVzWc969gGW3ub9Y0nSMCS0iv-ZGAuaJyZ457DM2U_pCAtQ==&ch=mh163X76rV0UZZ1kJoLjswWeoAyw_64dR2owLiae5w7YmfdQY0tS8A==>

[Ask a question]<http://r20.rs6.net/tn.jsp?f=001oVUPqRwvmhHyJe1NramVOyFsb1nQaRLEH51PlaHXGavnPY7BhzC9QzvbUDAAWvBK7EHDlEHfsHta8dT8gNltLccIbi7HfaxuCNBAkytr81Ohds5iQUF_EbwRXwrcObGKDXOfrb7VEQAZcHgHFrpmCIlBUTrKsEDkvRi1g3kPA70=&c=WdOyO5ZZVzWc969gGW3ub9Y0nSMCS0iv-ZGAuaJyZ457DM2U_pCAtQ==&ch=mh163X76rV0UZZ1kJoLjswWeoAyw_64dR2owLiae5w7YmfdQY0tS8A==>

Copyright 1996-2020 Shared Results International. Published weekly. Distribution is limited by license. For information on how to include additional recipients, contact support at tech-talk.com<mailto:support at tech-talk.com> 941-355-2092.

The Tech-Talk e-newsletter for Oregon library staff is distributed weekly via the Libs-OR and TechTalk mailing lists. Know someone who might be interested in receiving a copy but they’re not on TechTalk or Libs-OR mailing lists? 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!

Tech-Talk is a paid subscription service for staff of Oregon libraries and 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
https://libguides.osl.state.or.us/coronavirus

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<https://fb.me/StateLibraryOR> | Twitter<https://twitter.com/StateLibraryOR> | Instagram<https://www.instagram.com/statelibraryor/> | Pinterest<https://pinterest.com/statelibraryor/>

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/techtalk/attachments/20200519/644f83e8/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/techtalk/attachments/20200519/644f83e8/attachment.png>