Linking Tips

Quick reference

• 00:04 This next session can be a little controversial sometimes.
• 00:07 It's frowned upon to link files in some quarters
• 00:10 of the financial modeling community.
• 00:14 >> Quite frankly though, sometimes it just can't be helped.
• 00:18 It may be, you've got files that are far too large to put them all in one workbook.
• 00:22 It may be that people need to work on them in different locations.
• 00:26 It may be some of the data is confidential.
• 00:28 It could be that they have sort of different purposes and so on and so
• 00:32 on and so on.
• 00:34 Look sometimes linking models is just a fact of modeling life.
• 00:38 Let's take the two examples here, Workbook 1 and Workbook 2,
• 00:42 that just coincidentally have the same number of sheets.
• 00:46 Don't read anything that.
• 00:48 Now, most people,
• 00:48 when they're linking workbooks, use the industry standard URM approach.
• 00:54 Never heard of it?
• 00:55 I'm sure you have, I'm sure you use it.
• 00:58 Look, let me give you an example.
• 01:00 It may be that Sheet 1 from Workbook 1 links into Sheet 1 in Workbook 2.
• 01:06 And also Sheet 2 has something that links into Workbook 1.
• 01:09 And by the way, there's something from Sheet 3 that works into Sheet 5,
• 01:13 something from Sheet 4 that links into Sheet 3,
• 01:16 something from Sheet 5 that links into Sheet 7.
• 01:18 And Sheet 6 links into Sheet 4, and Sheet 7 links into Sheet 2, my goodness, no!
• 01:25 Don't do this, we're out of control!
• 01:26 I don't know what should be linked where.
• 01:29 How am I going to maintain it?
• 01:31 Or what if there's something from Workbook 2 that links back into Workbook 1?
• 01:35 You could have a circular reference and not even realize it.
• 01:38 This is crazy.
• 01:39 Let's start again.
• 01:42 A better approach is before we start off is to create a worksheet in Workbook 1
• 01:47 where all the actual references that are going to be exported will be contained.
• 01:53 We'll call that an export sheet,
• 01:55 we'll create a mirror image of this as an import sheet in Workbook 2.
• 02:00 Now find all the things you need to link in Sheets 1 to 7 or Workbook 1.
• 02:04 And feed them first into the export sheet.
• 02:07 Then link those into import on Workbook 2.
• 02:12 That can then be disseminated into the workbook, be controlled, and
• 02:17 you have one place where you can find all the links.
• 02:20 Exporting Workbook 1, importing Workbook 2.
• 02:23 Easy, I think it's time to go back to our good old Excel template
• 02:28 file you should be getting familiar with now.
• 02:31 This time I'm using a variant that's going to illustrate the export, import idea.
• 02:38 So, here's my base template form.
• 02:41 Let's take a quick look at it first of all.
• 02:43 I'm gonna click on the Navigator tab here.
• 02:46 And you can see, I've got three sales assumptions sheets here, domestic,
• 02:51 overseas, and underseas, and an export sheet.
• 02:54 So let's take a peek.
• 02:57 If I click on the domestic one, first of all, you'll see I've got sales volumes for
• 03:00 domestic product.
• 03:04 And if I go to the next sheet, I have corresponding values for
• 03:08 overseas, and the next one for whatever underseas is.
• 03:12 Yes, thank you Liam for
• 03:13 the new terminology I've introduced to the English language.
• 03:17 The next sheet's interesting as well, the Export tab.
• 03:20 This is the sheet we need to, if you recall,
• 03:22 where you bring in everything you're going to export in.
• 03:25 So I wanna clear we're going to export domestic but you know what I mean,
• 03:30 we're going to export to another workbook.
• 03:32 I've chosen the delightfully named Gottlieb and Rancid products,
• 03:36 I'm sure they're big sellers.
• 03:39 Here this links to row 23 of the domestic sales assumption worksheet.
• 03:43 This one to row 34 and something I've linked things from the overseas
• 03:48 worksheet 2 row 25 for Indigo, row 40 for Xylophone, etc.
• 03:53 So what I need to do is create a mirror image of this for
• 03:56 the import sheet in the alternative workbook.
• 04:00 To do that, I need to create a new Excel workbook,
• 04:05 which I'll do by going Ctrl+N.
• 04:08 Here's my new Excel workbook, this is where I want to make a copy of the data.
• 04:14 Now if I just go copy and paste, I wanna create links,
• 04:16 I'm gonna bring in all the styles and everything.
• 04:19 I don't want that, I want to go copy paste special.
• 04:22 Now you might think I'm going to paste special formulas there and
• 04:27 maybe formats afterwards.
• 04:29 No, you'd be wrong.
• 04:30 And the reason being, if I copy and paste special as formulas,
• 04:34 I'm actually going to link back to the sheets,
• 04:38 the domestic, the overseas and the underseas assumption sheet.
• 04:42 Because that's what the current export sheet is doing.
• 04:46 Don't want to do that.
• 04:47 What I'm going to do, surprisingly, is copy and
• 04:51 paste special, get this, as values.
• 04:55 And then put formats in afterwords.
• 04:57 Let me show you.
• 04:59 So let me go back to the other one.
• 05:01 So this is the export sheet.
• 05:04 I'm going to highlight this entire sheet.
• 05:07 The way to do that is go and click on the top left-hand corner and
• 05:11 then go Ctrl+C, and that's copying the whole thing.
• 05:16 And now I'm going to go back to the other workbook, Alt+Tab,
• 05:19 and Paste Special as values.
• 05:25 So into here and go down to values and press Enter.
• 05:32 Now I've done that, I want to Paste Special as formats too.
• 05:38 Alt+E+S+T, Enter, voila!
• 05:44 Yes, I've got the grid lines.
• 05:45 Yes, it looks slightly different 'cause it's a slightly different workbook
• 05:48 formatted differently.
• 05:49 I've got the colors a little off here.
• 05:51 But you get the idea.
• 05:53 This is what it is.
• 05:53 I can put a frozen pane in here.
• 05:56 It's very easy to do.
• 05:57 All I've got to do is go up to the View tab, into Freeze Panes.
• 06:01 Freeze Panes as we did before.
• 06:03 I could even switch the grid lines off and make it look a little neater,
• 06:06 should you wish.
• 06:07 And everything here is a value.
• 06:13 Why have I done that?
• 06:14 Well, now I'm gonna go back here, and I'm going to go = and
• 06:18 link back to the other workbook.
• 06:23 Click on that.
• 06:24 And change this, get rid of the $ sign.
• 06:27 So it's just A1s, so copy it, press Enter.
• 06:29 Now, let's copy that with Ctrl+C.
• 06:33 Now before I paste anywhere, I'm going to bring the F5 function key up, or
• 06:38 Ctrl+G, to bring up the Go To dialog box, and click on Special.
• 06:44 And I want to choose Constants, and I want it to be Numbers, Text,
• 06:48 Logicals, Errors, all the values it finds on here.
• 06:51 If I click OK, it's now selected all of those.
• 06:56 And now I can Paste Special as formulas into there.
• 06:59 That's Alt+E+S+F, for formulas, and then click OK.
• 07:05 Voila, everything here now links back to the other workbook,
• 07:10 in the same place, but nothing in any of the blank cells.
• 07:15 That's a pretty cool trick.
• 07:16 And now I've got a complete link,
• 07:18 and I change this to import, change the name of this file, rename this, I'm done.
• 07:23 Very easy way of creating an import sheet from an export one, I think you'll find.

Lesson notes are only available for subscribers.