• We’re currently investigating an issue related to the forum theme and styling that is impacting page layout and visual formatting. The problem has been identified, and we are actively working on a resolution. There is no impact to user data or functionality, this is strictly a front-end display issue. We’ll post an update once the fix has been deployed. Thanks for your patience while we get this sorted.

Word/Excel Help Needed, Please

K

KarenMarie

Elite Member
Ok, my father owns a small business and I work for him. When he started the company about 5yrs ago, i did all his invoices, brochures, business cards, etc. At the time, I only had MS Works, so used that.

For the past few years, I have been using MS Office 2003 and it was a total pita to get it working, as they were Works files. The invoice is a document with an excel sheet embedded (pasted) into it... so I had to install Works to get the excel part of the document to work in Word.

I want to re-do the whole thing in Word. It is a Word Document Template with graphic borders. But the problem I am having is with the new Excel spreadsheet that I made. It has formulas and shading and etc.

I don't know how I am supposed to do it.

I got the document part of it right, with the borders and stuff. But no matter if I insert object or copy and paste... it is a mess. The sizing is not right and colums, rows and cells that I DONT want appear anyway, along with scroll bars. (I did the whole print area thing in excel)

Basically, what I need to do and cannot.... is replace the of old Works spreadsheet with the new excel spreadsheet.

I have the files uploaded online.... so if anyone is interested in helping me I will be happy to provide the links.

Thanks in advance!

🙂

 
Paste special doesnt work the way i want it to.

not only doesnt it appear WHERE i want it to, but it still shows columns and rows that are not part of the print area.

i will try it again though, in case i over looked something.

 
hmm. I guess you have to provide links, hehehe. I'm still busy @ work coding, so that might have to wait 'till I get home. (and occasional nef'ing on AT? :Q 😱)
 
i would like to give a huge and public THANK YOU!! to JCKC for doing this for me. And much appreciation for every one that tried to help me.

I am grateful!!



------------------------------------------------
Excel

invoice

Basically, I just need to get rid of the spreadsheet on the invoice and replace it with the new excel one. The format of the cells contain formulas. Gratuity is set to be 20% of the charges and it will all total across. Also, charges will total down, grat will total down and total will total down. Those totals will total across.

i would like it to look the same though.

i really appreciate your help.

🙂
 
Originally posted by: KarenMarie
Excel

invoice

Basically, I just need to get rid of the spreadsheet on the invoice and replace it with the new excel one. The format of the cells contain formulas. Gratuity is set to be 20% of the charges and it will all total across. Also, charges will total down, grat will total down and total will total down. Those totals will total across.

i would like it to look the same though.

i really appreciate your help.

🙂
Click to expand...

hmm...either i'm being super retarded, or...I just can't seem to open that book1.zip file into excel. The XML looks more like a series of unaltered xml templates?

 
Ahhh... crap!!

The excel file was done on my laptop which has the office 2007 beta. i cannot open on my desktop that is running 2003 pro, either.

is there a converter for it? I downloaded one and i STILL cannot open it.

🙁
 
I downloaded the Excel file and it was corrupted. Excel 2007 was able to repair it, but I'm not sure that I got everything. Anyway, from what I can see it is a very simple spreadsheet and you can duplicate it using only Word. Just use formulas in a table exactly like you would in Excel. I copied the Excel data into Word as a table and added a formula. Worked like a charm. Since you are using a template, you've only got to set it up once.
 
Originally posted by: mayest
I downloaded the Excel file and it was corrupted. Excel 2007 was able to repair it, but I'm not sure that I got everything. Anyway, from what I can see it is a very simple spreadsheet and you can duplicate it using only Word. Just use formulas in a table exactly like you would in Excel. I copied the Excel data into Word as a table and added a formula. Worked like a charm. Since you are using a template, you've only got to set it up once.
Click to expand...

wow good job! you beat me to it...i got delayed haha...

he's right. you saved it as a office 2007 file. you can have the option to always save it in a office 97-2003 format.
 
Ok, I redid the spreadsheet on the desktop using excel 2003

Excel

What you guys are trying to explain to me is that I need to do it all over again, in Word, as a table? Hahaha... that took me ages!!!

The gratuity is auto figured to be 20% of the charges... on every line... only if something is entered into the charges cell. that alone took me ages. Then the total was summed of charges, gratuity and tolls/parking... and everything is totaled again at the bottom.

Oh man... i have never done a table in Word before.

is there no way I can just insert the excel sheet?

If not... looks like I know what I am doing tomorrow.

🙂
 
Ok, so i started from scratch in Word, inserted a table and got it all going EXCEPT for the 20% gratuity.

the forumula I want to use is:

=IF(ISNUMBER(F2),F2*0.2,")

But I do not have that option. I get"

!Syntax Error, (

sorry if there is an obvious answer to this... i have never done it before.

 
It should be this way:

=IF(ISNUMBER(F2),F2*0.2,"")

You just need one more " at the end before the last )
 
Originally posted by: bruceb
It should be this way:

=IF(ISNUMBER(F2),F2*0.2,"")

You just need one more " at the end before the last )
Click to expand...

yes, i tried that. but it is the option to insert it that i cannot find. i have a very limited choice.

in 2003 i can find AUTOSUM, that's it. And in 2007, I have a few more options, but percentrank is not one of them. I have checked thru all the add/remove buttons and cannot find how to add the option to do more formulas than just AUTOSUM


🙁
 
ok, i did a search of help.

they say...

go to the cell... choose the TABLE MENU... FORMULA... now a little box appears. I put the formula into the box that says FORMULA ... and i leave the rest of the boxes blank. And i get Snytax error.

I will do a print screen... brb.

EDIT Pic

Not sure what else i a suppose to do, but the results of what I am doing is a SYNTAX ERROR.
 
I have not used Office 2007 as yet ... really can't say what they changed
In Word 2002 there is a number drop down in the formula box .. perhaps
something in there needs changing

If you can get it to work properly in Office 2002 or 2003 .. then just use that
system ... you should be able to use older style file in Office 2007
 
it does not matter if i use office 2003 or 2007. i have a copy of this on my desktop (office 2003) as well as the laptop (2007).

I also cannot figure out how to make the charges, gratuity, tolls/parking and total be currency instead of text. i wonder if that is a contributor to the problem.

when i do this in excel, i make date as a date, description as general text, time in/out as hourly am/pm and the rest as currency.

i do not know how to do that in a word table. i am sitting at the laptop right now, and have been over EVERY setting i can find. this is driving me crazy.

hahaha.

oh... and although i am using the laptop with 2007, i have it saved as a 2003 document.
 
bruceb...

that is sooooo not working for me. it put an excel at the top of the page, over the rest of the stuff, cannot be moved and is the wrong size. no matter what i do... when i go to print, 1/2 of it is gone.
 
Karen,

As far as I know, Word does not support ISNumber, and the If statement can't return a blank. If I wanted to do this entirely in Word, I would do this instead:

1) In the Gratuity cell the formula is =F2*0.20. You'll want to copy that to each row, and then edit each formula to change the row number (e.g., =F3*0.20 and so on). Unfortunately, all references are absolute so you have to edit them manually.

2) For the Total field, the formula is =Sum(F2:H2), and you'll have to change the row numbers in each row again.

3) For the grand total at the bottom use = Sum(Above).

Again, since this is a template you only have to set it up once, so the above tedious steps aren't so bad.

Now, I think that you want to hide the rows that don't have any data. The only way that I can think of to do that is to highlight the blank rows and change the font color to white (assuming that you are not using a color printer, or at least using white paper). You would do this after filling out the invoice. It takes just a couple of seconds. You can use the font color button on the formatting tool bar.

I think this should work perfectly. If you have any problems, send me a PM.

Tim
 
Karen,

I forgot to mention one other thing. To recalculate the table, just select the whole thing and then press F9. You can also do this with individual cells, or small groups of cells.

Tim
 
You must log in or register to reply here.

TRENDING THREADS

Back
Top