Excel Spreadsheet in Word problem (XP)

Topic

New Reply

I have taken a spreadsheet that I made and put it in a Word document. But now I need to have the ability to enter a number at a spot in a paragraph that is above the spreadsheet and have that number appear in a cell in the spreadsheet. Someone said there is a way to do it with a bookmark, but did not know the details.
Thanks

Reply | Quote

Replies

This is tricky and very vulnerable. I would avoid it if possible. Bookmarks in a Word document tend to disappear if you type over them.

Reply | Quote

Thanks for the info. If the bookmarks have a tendency to disappear, then the people I am working with will be sure to do that, of course.
But I do need to see if I can make this happen somehow, though. Can I create a single cell spreadsheet and embed it in the paragraph, and then have the number inserted there appear in my worksheet below?

Reply | Quote

It might work if you create a workbook in Excel, save it and paste links to parts of the workbook into your document. I coulldn’t get the data to update consistently with embedded spreadsheets, but perhaps someone else knows how to do that.

Reply | Quote

What I am wondering is if I embed 2 separate small worksheets in a Word doc, can I reference cells in Worksheet A in formulas that are in Worksheet B?

Reply | Quote

As far as I know, you cannot refer to cells in another embedded worksheet in a formula the way you can refer to cells in another workbook. You can paste a link to a cell, but as I wrote in my previous reply, that didn’t update consistently. But perhaps I’m missing something obvious.

Reply | Quote

Maybe I will play around with doing it with the bookmark, even though it is risky. Do you know how to reference a number in a Word bookmark when I am creating a formula in an embedded spreadsheet?

Reply | Quote

Copy the number in the Word document to the clipboard, and preferably a space before and after it to reduce the chance of accidentally deleting the bookmark that will be created below.
Activate the embedded worksheet.
Activate a target cell.
Select Edit | Paste Special…
Select Paste Link, and select Text as type, then click OK.
Word will create a bookmark for the number automatically
You can refer to the target cell in formulas in other cells.
If you change the number in the document, you can force an update by activating the worksheet and selecting Edit | Links, selecting the link and clicking Update Now.

Reply | Quote

Thanks for details. I will play with this a bit, but unfortunately it may not work for my present situation. I need something that would update automatically when the number in the document is changed. I am dealing with sales people who change an item at the top, depending on the client, and then I need for the worksheet below to change automatically. It’s too much to expect these sales people to remember to update the link.

Reply | Quote

Why put it in a word document? Why not just keep it in an excel spreadsheet?

Steve

Reply | Quote

It has 2 pages of text in addition to the spreadsheet, and figures in the text need to be referenced in the spreadsheet.

Reply | Quote

Can’t you create the text output in excel with the chart and its data?

It seems to be a compromise between the two. I don’t know enough about what you are doing to judge, but excel can handle text (I think) better than word can handle calculations.

But the real question is do you use more WORD features or EXCEL features and how well can the “features” from word be emulated in excel and how well can the features in excel be emulated in word? Whichever one handles the brunt of the problems the best should be the application of choice even if you must do workarounds.

Steve

Reply | Quote

I’m willing to look into doing it that way, but I am not familiar with the text capabilities of Excel. Can I create a text box so I am not putting text in small cells? I have 2 pages of text.

Reply | Quote

Yes, you can even have the contents read from cell contents (if desired). If you create a formula instead of “hard Text” your formatting options are limited, however.

Can you post an example of what you want with non-proprietary info, but of comparable info, so we can get a better idea of the goals and we can better suggest.

I will warn you, I don’t use word, so my solutions will tend to be excel (or excel VBA) solutions. (you know what they say, “If all you have is a hammer, everything looks like a nail”

Steve

Reply | Quote

OK here it is

Reply | Quote

How about something like this (or do you want text boxes?) I am unclear what the text is for.

Steve

Reply | Quote

How about something like this (or do you want text boxes?) I am unclear what the text is for.

Steve

Reply | Quote

Attached is a spreadsheet with three text boxes – one for each of the text you had in your Word doc plus two number cells. It just shows you to create a text field (which can be any size, with or w/o borders, colored, etc.) I agree with the others that it seems your work would be much easier in Excel than Word (but we don’t know enough about your project to make sure).

Deb

Reply | Quote

Attached is a spreadsheet with three text boxes – one for each of the text you had in your Word doc plus two number cells. It just shows you to create a text field (which can be any size, with or w/o borders, colored, etc.) I agree with the others that it seems your work would be much easier in Excel than Word (but we don’t know enough about your project to make sure).

Deb

Reply | Quote

OK here it is

Reply | Quote

Yes, you can even have the contents read from cell contents (if desired). If you create a formula instead of “hard Text” your formatting options are limited, however.

Can you post an example of what you want with non-proprietary info, but of comparable info, so we can get a better idea of the goals and we can better suggest.

I will warn you, I don’t use word, so my solutions will tend to be excel (or excel VBA) solutions. (you know what they say, “If all you have is a hammer, everything looks like a nail”

Steve

Reply | Quote

Can’t you create the text output in excel with the chart and its data?

It seems to be a compromise between the two. I don’t know enough about what you are doing to judge, but excel can handle text (I think) better than word can handle calculations.

But the real question is do you use more WORD features or EXCEL features and how well can the “features” from word be emulated in excel and how well can the features in excel be emulated in word? Whichever one handles the brunt of the problems the best should be the application of choice even if you must do workarounds.

Steve

Reply | Quote

Just my two cents. If you are planning on using bookmarks in Word, I recommend going to Tools, Options, View (in the Word doc) and turning on the view of bookmarks. It won’t prevent deletion – but makes it less likely to delete something you can now see!

Reply | Quote

Thanks to everybody for the direction and ideas!
Just to clarify, the document is one that I created to save time when the sales person makes a proposal to a potential client. Presently, the first two pages of my document is text that I created from which they can pick and choose which paragraphs to use, depending on the client and situation. They delete the parts that do not apply. There are two pricing figures that the sales person puts in these paragraphs that need to be then included in the spreadsheet at the end of the document.The ideal situation (I initially thought) would be bookmarks whose contents then appear in the spreadsheet, except that Hans said above that they would not update automatically.

It seems like the solution is to use Excel with text boxes for parts of the first two pages, with some text in cells for the areas with the numbers that need to be transferred to the spreadsheet. Then have the spreadsheet part with the calculations at the bottom. I’m just afraid it may become a formatting nightmare to create it initially. The problem is that it needs to have a nicely formatted appearance after the sales person deletes the various unwanted parts of the text (which works well in Word, not sure about text boxes). I will try working with the text boxes and see how well it goes.
Thanks again.

Reply | Quote

In addition to the textbox concept:
If the idea is to toggle paragraphs on/off, you could put checkboxes in by each paragraph and hide the textboxes or even the rows (if you put the text into cells) when unchecked, and expand when unchecked.

Steve

Reply | Quote

Steve, I am not familiar with using checkboxes, and Excel Help is no help. Can you explain, or point me someplace with a tutorial?
Also, I noticed in the file you attached Excel told me there were macros in it. What function did the macros play?
Thanks

Reply | Quote

post 320321 has some good excel and VB tutorials and general info.

I am not sure exactly (though I have an idea) of what you want, so I am hesitant to try example code. COuld you describe more what you are trying to do and maybe we could give some examples.

Steve

Reply | Quote

post 320321 has some good excel and VB tutorials and general info.

I am not sure exactly (though I have an idea) of what you want, so I am hesitant to try example code. COuld you describe more what you are trying to do and maybe we could give some examples.

Steve

Reply | Quote

OK – you say “code” and I say “never mind”.

By the way, what about the macro in your example?

Reply | Quote

[indent]

---

By the way, what about the macro in your example?

---

[/indent]

Sorry, it wasn’t supposed to be in there. I must have added a text box while playing and accidently hit view code, since I got code to work on click event. It can be deleted.

Without code, The only way I could see doing it with autofiltering perhaps. if you wanted to select certain paragraphs and have them be “removed” automatically and shrink the rows, but this would involve setting up the spreadsheet correctly to work and again I have few details or sample worksheet to work with.

Steve

Reply | Quote

OK thanks. I’ll work with it without checkboxes, and see what I come up with.

Reply | Quote

OK thanks. I’ll work with it without checkboxes, and see what I come up with.

Reply | Quote

[indent]

---

By the way, what about the macro in your example?

---

[/indent]

Sorry, it wasn’t supposed to be in there. I must have added a text box while playing and accidently hit view code, since I got code to work on click event. It can be deleted.

Without code, The only way I could see doing it with autofiltering perhaps. if you wanted to select certain paragraphs and have them be “removed” automatically and shrink the rows, but this would involve setting up the spreadsheet correctly to work and again I have few details or sample worksheet to work with.

Steve

Reply | Quote

OK – you say “code” and I say “never mind”.

By the way, what about the macro in your example?

Reply | Quote

Steve, I am not familiar with using checkboxes, and Excel Help is no help. Can you explain, or point me someplace with a tutorial?
Also, I noticed in the file you attached Excel told me there were macros in it. What function did the macros play?
Thanks

Reply | Quote

In addition to the textbox concept:
If the idea is to toggle paragraphs on/off, you could put checkboxes in by each paragraph and hide the textboxes or even the rows (if you put the text into cells) when unchecked, and expand when unchecked.

Steve

Reply | Quote

Thanks to everybody for the direction and ideas!
Just to clarify, the document is one that I created to save time when the sales person makes a proposal to a potential client. Presently, the first two pages of my document is text that I created from which they can pick and choose which paragraphs to use, depending on the client and situation. They delete the parts that do not apply. There are two pricing figures that the sales person puts in these paragraphs that need to be then included in the spreadsheet at the end of the document.The ideal situation (I initially thought) would be bookmarks whose contents then appear in the spreadsheet, except that Hans said above that they would not update automatically.

It seems like the solution is to use Excel with text boxes for parts of the first two pages, with some text in cells for the areas with the numbers that need to be transferred to the spreadsheet. Then have the spreadsheet part with the calculations at the bottom. I’m just afraid it may become a formatting nightmare to create it initially. The problem is that it needs to have a nicely formatted appearance after the sales person deletes the various unwanted parts of the text (which works well in Word, not sure about text boxes). I will try working with the text boxes and see how well it goes.
Thanks again.

Reply | Quote

Just my two cents. If you are planning on using bookmarks in Word, I recommend going to Tools, Options, View (in the Word doc) and turning on the view of bookmarks. It won’t prevent deletion – but makes it less likely to delete something you can now see!

Reply | Quote

It has 2 pages of text in addition to the spreadsheet, and figures in the text need to be referenced in the spreadsheet.

Reply | Quote

Why put it in a word document? Why not just keep it in an excel spreadsheet?

Steve

Reply | Quote

Thanks for details. I will play with this a bit, but unfortunately it may not work for my present situation. I need something that would update automatically when the number in the document is changed. I am dealing with sales people who change an item at the top, depending on the client, and then I need for the worksheet below to change automatically. It’s too much to expect these sales people to remember to update the link.

Reply | Quote

Copy the number in the Word document to the clipboard, and preferably a space before and after it to reduce the chance of accidentally deleting the bookmark that will be created below.
Activate the embedded worksheet.
Activate a target cell.
Select Edit | Paste Special…
Select Paste Link, and select Text as type, then click OK.
Word will create a bookmark for the number automatically
You can refer to the target cell in formulas in other cells.
If you change the number in the document, you can force an update by activating the worksheet and selecting Edit | Links, selecting the link and clicking Update Now.

Reply | Quote

Maybe I will play around with doing it with the bookmark, even though it is risky. Do you know how to reference a number in a Word bookmark when I am creating a formula in an embedded spreadsheet?

Reply | Quote

As far as I know, you cannot refer to cells in another embedded worksheet in a formula the way you can refer to cells in another workbook. You can paste a link to a cell, but as I wrote in my previous reply, that didn’t update consistently. But perhaps I’m missing something obvious.

Reply | Quote

What I am wondering is if I embed 2 separate small worksheets in a Word doc, can I reference cells in Worksheet A in formulas that are in Worksheet B?

Reply | Quote

It might work if you create a workbook in Excel, save it and paste links to parts of the workbook into your document. I coulldn’t get the data to update consistently with embedded spreadsheets, but perhaps someone else knows how to do that.

Reply | Quote

Thanks for the info. If the bookmarks have a tendency to disappear, then the people I am working with will be sure to do that, of course.
But I do need to see if I can make this happen somehow, though. Can I create a single cell spreadsheet and embed it in the paragraph, and then have the number inserted there appear in my worksheet below?

Reply | Quote

This is tricky and very vulnerable. I would avoid it if possible. Bookmarks in a Word document tend to disappear if you type over them.

Reply | Quote