Skip to main content

How to Create Reusable Content - Reuse Existing Spreadsheets

See more videos and subscribe to the Proposal Kit YouTube channelProposal Kit How-to Videos

This is Part 5 of 5 on how to reuse content when creating documents with Proposal Pack Wizard software. If you have existing Excel spreadsheets you use for your business or you need to create one or more custom Excel spreadsheets that you want to reuse for the documents you generate in our Wizard software with our Proposal Kit templates this video will show you how. You can link any cells of any spreadsheets you create to our Word templates so your generated proposal, business plan, or other type of document has links to the spreadsheet. This lets you do your calculations and manage your data in a spreadsheet with the nice formatting of the Word document for the final output using Word and Excels linking feature that lets you auto-update the Word document from the spreadsheet cells.

In this video we're going to show you another way to reuse existing content in the Proposal Kit. This video is going to focus on reusing an existing spreadsheet. The Proposal Kit includes stock spreadsheets already pre–made but you might have an existing spreadsheet that you've created for your business that you want to use instead of customizing ours.

Now this system will work for any spreadsheets no matter what they are. They could be for quotes, schedules, budgets or anything you've created a spreadsheet for you can integrate into your proposals created by our Wizard. How you do that is you take your spreadsheet and copy it into our Documents folder with a specific name and then you'll have to customize one of the Word templates that gets built into the generated document so that the Wizard can make links between your spreadsheet and its generated documents.

We're going to illustrate this example with a photography services company and I'll show you the stock spreadsheets and templates and how they'll be replaced with your custom versions. So in any Proposal Pack, it doesn't matter what design theme, you'll have a Documents folder and Spreadsheets in a folder. We're going to illustrate this with a custom estimate spreadsheet so you'll see we already have an estimate calculator.

If we open that you'll see it's already predefined with line items, prices, quantities, calculations, totals and so on. But in our situation we want to reuse an existing spreadsheet that someone has already created. In this case it also has a matching Word template.

So the estimate calculator will have a matching estimate template where these tags will be replaced with links directly to those cells in the spreadsheet. In our example here this company has their own custom spreadsheet that they want to use instead. So you can use your existing spreadsheets as–is without having to make any changes.

The only changes you might have to make would be in the formatting so that the end result that gets linked in from the spreadsheet to the Word document has the correct formatting such as dollar signs, date formats and so on. So the first thing we're going to do is we're going to save our custom spreadsheet over the top of our existing estimate calculator. So we're just going to save it as the exact same name and overwrite the Proposal Pack's EstimateCalculator by doing a Save As.

That's really all there is to it. Now you'll see this has been updated. This is our custom calculator now in the Proposal Pack's Spreadsheet folder.

The second thing we'll want to do is while we don't have to make any changes to the Excel spreadsheet we will have to make changes to the Proposal Pack's Estimate.doc template. We're only illustrating this with the estimate template and the estimate calculator.

You can overwrite any of our calculators that are tied into any of our templates and you can also integrate a custom spreadsheet into any of our templates even if it doesn't already have a predefined spreadsheet. So there's plenty of other help information on how to really customize the Excel spreadsheets. Okay so we found the Estimate.doc template in the Proposal Pack.

It's illustrated with one of our photography design themes. So this is the stock template and we've already pre customized this one to match. So we've removed the instructional text in red here.

And you'll see we've modified the tags and the headers and these cells to match our custom information. So what we're going to do is we're going to take our custom Estimate.doc template.

We're going to do the same thing. We're going to overwrite the Proposal Pack's estimate spreadsheet. Or you can edit our templates in place.

There's many ways to customize this. We're going to replace the existing estimate spreadsheet with this customized version. Now there's plenty of extra help information on how to do the linking and set up the tags.

But just really quickly we'll illustrate this with this custom example. So this custom spreadsheet has rows and columns. so A, B, C that will be column 1, 2, 3 and so on.

And then the row numbers. So what we've done is we've edited the estimate template and we've put rows 8 through 36 you'll see match up with rows 8 through 36 and column 1 as A column right here. So when the Wizard generates a document it's going to pull in all of the line items from all of these cells and place them in this Word table.

It will do the same thing for the Estimated Hours which is column 3 and the Estimated Price which is column 5. Which will be the E column. The total, say R6C8 that'll be row 6 column 8 over here and so on.

The Additional Costs rows 23 to 35 column 7 will be these cells and so on. This company has their own custom payment plans where they have an upfront payment or a two part payment. So you can integrate this directly into the proposal by just linking whatever cells you have in your custom spreadsheets to the cells in the Word template.

So once you've customized your Word template to link all the cells that you want out of the spreadsheets in the appropriate places we are ready to actually create a document. So during the Wizard's assembly process it will make a copy of your custom spreadsheet that we're reusing into each new proposal folder. And we use the word 'proposal' pretty interchangeably.

It just means a document. That really could be a business plan, report, study, any Word document you need to create. Proposals just happen to be the most common document created.

All right, So now we're ready to actually put this into use. We're going to create a Document Project. And we're checking a Cover Letter, Title Page, Back Page, a Table of Contents, an Introduction, the Estimate, a Company History and Services page.

So when we build this document the Wizard is going to piece all these pages together and it will make a copy of the custom spreadsheet and link it directly into the Estimate page of the generated document. Okay, now our document has been generated and because we're using spreadsheets you'll see a message where Word is asking if you want to update the document from the links. And so you'll want to say Yes.

This is a standard part of Word where it's going to update when you open the document from the spreadsheet. So you see we've got the photography design theme, Table of Contents, the chapters we selected and we get to the Estimate. So you can see it used our custom Estimate table and all of those tags with rows and columns have been replaced with actual links.

So now we can customize this. We've used in this sample a number of rows that we just need to remove from the final version. So to do that we'll just select and delete.

We're just manually editing this Word document. And now we have a finished version ready to send out. All the calculations have been done in the spreadsheets and the cells are directly linked into the Word document.

I will show you how this update system works in Word between linked spreadsheets. So we'll save this Word document. This is the easiest way to do this with Word.

Is to close the Word document, make changes to the spreadsheet and then reopen the Word document. So let's just change our estimates. I'm going to increase the price on the first line item to a very large number you can see the update.

So you know you can see a substantially larger number. Then we just go back and reopen the Word document, say Yes when Word asks if you want to update from the linked spreadsheet. And you'll see the Word document is automatically updated from the spreadsheet.

So you can use this with any spreadsheets you create. We'll show a second example that's a little more simplified using a template that does not have a premade spreadsheet already associated with it. So in this example let's say we're going to use the Options page.

Now how the naming convention works is the name of every template has the word 'Calculator' added to it and that's the matching spreadsheet name. So remember we had an Estimate.doc template and an EstimateCalculator.xls spreadsheet.

So in this example we want an OptionsCalculator.xls because we have an Options.doc template we're going to use. But there's no OptionsCalculator.xls that exists already so we're going to create a new spreadsheet.

We're going to call it OptionsCalculator.xls. We're going to save this in a backwards–compatible XLS format. Okay, so this is a completely blank spreadsheet and we'll just add some dummy data.

Okay, so we've added some rows and columns. This is just to show you that you can create a custom spreadsheet with anything in it. And now we're going to tie this to our Options.doc template.

So you see our stock template just has text. So what we're going to do, we're going to add a tag in here. Rows one through six column one.

Row one through six column two. This is rows one through six. A is column one. B is column two.

So that is all you have to do to tie a custom spreadsheet you want to reuse to a Proposal Pack document. All you do is edit one of our templates, add some tags to match the rows and columns, save the spreadsheet in a matching name that matches with the template. It's that simple.

Now that we have our custom Options spreadsheet and our custom Options template we're going to create a project and make use of that. I'm going to add a new project. And I'll click a Cover Letter, Title Page, Back Page and I want to search for the Options page.

So we're going to create a short document that really just has an Options page in it. Okay now we've created our second document. We're going to say Yes to update from the spreadsheet.

You see it opens this is a copy of the spreadsheet it made for this particular project and document. You scroll down to the Options page. You'll see it replaced our tags with links to the spreadsheet.

So this isn't direct text. These are actual OLE links and we can do the same kind of updating that we showed you earlier. If we make changes to the spreadsheet.

Save our spreadsheet changes. Reopen the document. Say Yes and Word has updated the Word document from the changed spreadsheet.

So this is an example of how you can reuse existing or custom content such as a spreadsheet and use it within the Proposal Kit.

Proposal KitPublished by Proposal Kit, Inc.