Software·43,750 discussions

Office suite Q&A II

Locked

Attention Office suite users and support analysts! If you joined us for the original Office suite Q&A discussion thread, welcome to part II. Our first discussion thread got so long we had to start a new one!

As with the original thread, use this space to consult with other Office users and analysts, post your questions, offer your comments on the Microsoft Office Suite TechMail tips, or make suggestions about future tips.

If you’d like to check out the first discussion thread for possibleanswers to a question you have, here’s the link:
http://www.techrepublic.com/forumdiscuss/thread_detail.jhtml?thread_id=122381

If you’re not subscribed to the Microsoft Office Suite TechMail, you can check it out here, under Technical Tips:
http://www.techrepublic.com/techmails.jhtml?repID=r001

*When pasting these links into your browser, remember to remove any spaces.

Topic

Word-specific questions

In reply to Office suite Q&A II

Post your MS Word-specific questions under this subthread.

W97 EQ Editor Question

In reply to Word-specific questions

I use an overstruck symbol regularly. It is the letter C overstruck with a ~. I use Insert > Field and then choose EQ editor, create the \O(c,~) and insert it into the document. Then I select the special character, right click, and toggle field codes on. Then I reformat the size of the C to 8 and the size of the ~ to 12 to get a proper looking symbol. Is there an easier way to do this with a macro or vb code? I can copy and paste the field while in the same document but in new documents I have to re do it. I’d like to be able to have keyboard shortcut to insert this custom symbol. Is it possible?

how about using a grouped text box?

In reply to W97 EQ Editor Question

Open a new document – and using 2 text boxes. first put the ~ then in the other put the C. Change the background to transparent, then you can group them together and save the document. Wala – there’s your symbol.

Put a shortcut to the document on one of your toolbars so that you can access it quickly and then copy and paste it into all your new documents with ease. You’ll never have to re-create the thing again. It’s a little work in the beginning, but it’ll save time in the long run.

Record a macro

In reply to W97 EQ Editor Question

Try going to Tools, macro, record macro. Then perform the steps you mentioned and then click the stop recording button. You can even assign a keystroke to the macro.

Bonnie

W97 EQ Editor Question

In reply to W97 EQ Editor Question

How about using the AutoText feature? Create the equation, then save it as an AutoText. Type in the assigned characters and the symbol should appear.

AutoCorrect is Fast

In reply to W97 EQ Editor Question

Highlight just the symbol with the desired formatting (no characters or spaces before or after). Click on Tools, Autocorrect. This automatically inserts the symbol (formatting intact) into the “With” space. Ensure that you click on “Formatted Text”,and “Replace as you type” has a check mark in front of it. Type a ~ (or any letter/s) in the “Replace” space. This will automatically work in Word, Excel, and Outlook if you are using “Word” as your email editor in Outlook.

Reply To: Office suite Q&A II

In reply to Word-specific questions

I would like advice on how to include large extracts from formatted documents into a document that I am composing without importing a whole lot of spurious Styles.

It seems the safest thing is to ‘paste special’ and choose unformatted text, but that can leave a great deal of reformatting to do.

Of course you might well ask all contributing authors to use specific Style but then you sometimes find you want to use historic documents too.

Is there some way of instructing Word to replace anamed style by another?

Brendan

simple solution

In reply to Reply To: Office suite Q&A II

just copy and paste the extraced para on the note pad given in accessories of windows start up this removes any formatting previously done and when u paste it on ur document it would be according to format which u have already set. 🙂

S&R styles

In reply to Reply To: Office suite Q&A II

The NotePad suggestion doesn’t do any more than pasting unformatted text.
Assuming the styles in the foreign text are named, try this instead. Paste the foreign text into your document normally. Then select that text, and do a Search & Replace on the old style names with the new names. When S&R dialog appears, click More then click Format to get to where you can select style names for the Find and Replace With entries.

Find and Replace – With More

In reply to Reply To: Office suite Q&A II

Brendan:

What you’re asking for is completely reasonable, and doable. Here’s how:
1) Go to Edit->Replace.
2) In the “Find what,” don’t type anything.
3) Click “More.”
4) When the second half of the dialog pops up, click the “Format” button.
5) Choose “Style.”
6) Choose the style you want to find.
7) Click in the text box for “With.”
8) Again, click “Format.”
9) Choose “Style.”
10) Choose the format you substitute.
11) Click “Replace All.”

You’re good to go then.

Let me know if you have any questions.

Cheers,
geoff

Use Normal Style

In reply to Reply To: Office suite Q&A II

These steps will change the imported text all to the Normal Style, which is your default font.

1.) Copy (Cntl+C) and (Paste Cntl+V) all the text into the Word doument.
2.) Select all the text that you just pasted (Cntl+A)
3.)Using the Formatting toolbar Click the drop-down arrow beside the title Normal (this is a Style)(If this toolbar is not turned on Right click in the Toolbar area and choose it from the list of toolbars or go to Customize to turn on the Formatting toolbar)
4.) Choose Normal from that list. This should immediately change all the text and formatting to Normal (default style)

Keyboard Shortcut for Normal Style

In reply to Use Normal Style

CTRL + SHIFT + n
Instead of all that clicking, click once in your text and then press the above keyboard combination. It’s fast and easy.

Letterhead template

In reply to Word-specific questions

I created a template in Word 2000 that contains our agency’s letterhead as a watermark in the header. The problem is that the letterhead appears on every page when I only want it to appear on the first page. How do I design the template to turn off the watermark (header) after the first page?

Letterhead Template

In reply to Letterhead template

1. When creating the template close the header after creating it.
2. Page down a few lines and “Insert Section Break-Next Page”.
3. Go into the Page Header and turn off the button for “Same as Previous”.
4. While on the second page, go into the Page Header and selct its contents and delete it.
You now have the watermark on only the first page.

Page Setup

In reply to Letterhead template

I’m on ’97, so I don’t know if this has changed with 2000, but here goes:
Go to File;Page Setup;Layout. Check the Different First Page box and click OK. When you View Header and Footer, there will be header for the first page only. You may have to copy your watermark into that header.

Header

In reply to Page Setup

The page setup described above will work for the first page (in 2000 or XP also), but you will also have to remove the watermark from the main header used on subsequent pages. You may need to put in a temporary page break (which you can delete after editing the header) to be able to access the main header after you have changed the layout to different first page.

Easiest Solution

In reply to Letterhead template

After thought and testing, decided you should edit your template and put a page break (my preference would be section page break so you can alter margins, etc.). Your second page would not have the watermark and would be formatted the way you wish.

If there is a more elaborate way, please let me know. Thanks.

Format first

In reply to Easiest Solution

This works for me:
In layout click different first page. You can then place anything in the header and it will not be repeated on subsequent pages.

Access-specific questions

In reply to Office suite Q&A II

Post your MS Access-specific questions under this subthread.

RTF documents from Access

In reply to Access-specific questions

I use ACCESS (XP) to hold records which have a number of memo fields in them. I have reports which print out these records. Each field is set to “can grow” and “can shrink”. The reports work fine. However if I convert to Word (RTF) or mail as RTF the RTF document often does not match the original. Lines of text end at differnt places and sometimes the the field is truncated. Has anybody seen this type of behaviour before and gor a solution?

re: RTF documents from Access

In reply to RTF documents from Access

Yes I have seen this same thing happen. I currently send all of my reports out using the snapshot format. This holds my formating. Users will need to have the snapshot viewer installed which is available on the office cd or they can download it fromthe Microsoft site. The draw back to this is the file is only an image they cannot access the data that is in the report to copy and past some where else. The good part about that is they also cannot alter your report.

Import Specification

In reply to Access-specific questions

I am using an import specification that I created to bring mainframe data in a text file into an Access 2000 table using a macro. It works well, but I need to look agin at the import spec to modify it’s properties. However, I can’t find a way to open it again so that I can see it. Is there any way anyone has found to do this, or must I create a new import spec to change any properites? (i.e. text/numeric fields, etc)

May be there’s a better way, but …

In reply to Import Specification

I’ve suffered similarly in the past, annoying isn’t it?
The only way I found was to start as if you are creating a new import spec. (File; Get external data; Import) You will then have to select a file, if you haven’t got a real one, just make yourself asmall text file and use that. When you get to the first wizard screen click Advanced, on the next screen click Specs and Open the one you want to change. From there on, just make the changes. When you are satisfied, use Save As to overwrite theold spec or give it a new name if you choose.

I haven’t done this for a while and I have Access XP but the screens look very familiar, I don’t think they have changed for the past two of three Access releases.

Works beautifully!

In reply to May be there’s a better way, but …

Your solution works beautifully. How ridiculous, to have to resort to using a dummy import using a dummy file, just to open an existing spec to alter it!
Thanks for your help!

Jpeg bitmaps in reports

In reply to Access-specific questions

Is there anyway to display a jpeg image in reports?
I want to use jpeg images of students and print reports with these pictures.

Excel-specific questions

In reply to Office suite Q&A II

Post your MS Excel-specific questions under this subthread.

add to default footers?

In reply to Excel-specific questions

My boss has a specific way he likes to see reports, but not all my reports go to my boss. I notice that under Page Setup / Header/Footer there is a set of ‘default’ footers, but none of them are appropriate for my need. Is there a way to add to this list of default footers? I have considered changing the footer the normal.dot file, or putting together a macro that will set up my footer, but I was wondering if there was a ‘better’ way to do this?

create a worksheet template

In reply to add to default footers?

set up your footers the way you want them, save the sheet under a generic name and then open that generic template. The footers will be prepopulated.

Filtering data

In reply to Excel-specific questions

I have an Excel spreadsheet that logs jobs. Sometimes the same job is worked on in separate fiscal periods. I want to be able to condense the log and combine all the same job names with summaries of other data collected. When filtering, I have discovered that if I have jobs called Bldg West First and Bldg West Second, these jobs will combine, even though they are not the same. How do I get the filtering tool to read the entire cell entry (an exact match) and not just part of it?

Pivot Tables

In reply to Filtering data

Try using Pivot Tables

try AutoFilter

In reply to Filtering data

If you click on any column heading, then select Data|Filter|AutoFilter, Excel will create dropdown boxes for each column which you can use to filter the records. This would distinguish between similar entries. (Hint: make sure there’s a blank row above your unduplicated column titles before selecting AutoFilter… Excel will figure it out from there)

Copying text as well as formatting

In reply to Excel-specific questions

Does anyone know how to copy the formatting, as well as the text, in Excel? When i have a particular cell equal to a value (A1= Hello) and then i have another cell reference that cell (B1= A1), all of the text is carried over, however, that formatting isn’t (subscripts, superscripts, font style, etc.). Is it possible to have a cell not just reference data in another cell, but rather a cell’s entire properties?

Format Painter

In reply to Copying text as well as formatting

Use Format Painter to prepare the target cell.

Format Painter can’t hack it

In reply to Format Painter

Format Painter is great and saves a lot of time, HOWEVER, it cannot handle cells that have mixed formatting. As a simple example, if you have a text cell that you have manually formatted so that the third character is red and bold, the rest having default formatting, the format painter will not copy this to any other cell.

total from one page to another.

In reply to Excel-specific questions

I have been trying to get the total from one sheet in a work book to transfer to the begining column of the next sheet in the same workbook. I don’t want to copy and patse special. I am unable to figure out how to do this.

total from one page to another

In reply to total from one page to another.

I suppose tranfer of total means changes made on sheet 1 should automatically refect in cell on Sheet 2. To do this on the Past Special dialog box you have use “Past Link” at the bottom.

satish hulyalkar

Total from one worksheet to another

In reply to total from one page to another.

Make the sheet that is to receive the total active.
Left click in the cell in which the total is to appear.
Enter an = sign DO NOT TOUCH ENTER KEY ANY OTHER TERMINATOR KEY.
Make the sheet that is sending the total active.
Left click in the cell with the total.
Touch ENTER key.
This establishes a link. Any change in the total of the sending sheet will automatically be updated in the receiving sheet.

Link technique can be used between workbooks as well as worksheets.
Mike

total from one page to another

In reply to total from one page to another.

Go to the second worksheet. Put a formula in the cell in which you want the total from thw previous worksheet to appear. You can use the “point & sheet” method to do that. Key the equal sign, select the previous worksheet, click on the cell containing the desired total figure, and press the Enter key to complete the formula. The total from the first worksheet now appears on the second worksheet. As its value changes, the revised figure is shown on the second sheet.

Reply To: Office suite Q&A II

In reply to total from one page to another.

For the value in sheet1 a10 transfer to Sheet2 a1
In sheet2 cell a1 enter =Sheet1!a10.
You can do the samething between workboks

Total from one page to new Macro

In reply to total from one page to another.

This may not be what you are looking for, but why not build a Macro? Try this for an example:
1. In A1 (Sheet 1) put in ‘5’, in B2 put in ’10’, in C1 put in the formula ‘=Sheet1!$A$1+Sheet1!$B$1’.
2. Now Select Tools -> Macros -> Record New Macro fill in the boxes, Excel will warn you when you save if you are trying to re-assign a short cut key. A small window with a stop button will appear on the sheet.
3. At this point you will be recording the macro. Right click on the C2 and select copy.Right click on the Sheet 2 tab at the bottom of the window, select Insert…, select new worksheet, select OK. Right Click on cell A1 in the newly inserted sheet and select paste special (You will only have to do this once to set up the macro, afterthis it will do it by the shortcut) and select Formulas, select ok. Click on the ‘[]’ button to stop recording the macro.
Now every time you hit ‘Ctrl-assigned key’ it will perform the whole process. Keypoints: for it to work so that if values in sheet 1 change and the values in sheet 2 are too change as well you must use the absolute reference commands in the sheet 1 formula (hence the Sheet1! and $ uses in my example). If you want only the value and it will not change in sheet 2 regardless of what happens in sheet 1, you can use ‘Value’ as the paste option in your macro versus the ‘formula’ option. This macro will copy the cell you currently have highlighted in one sheet and always place it in position assigned in the macro to the new sheet. Based on the example, if you highlight cell YY456 and use the macro, it will be placed in the new sheet at A1.
Hope this helps.

Thanks

In reply to total from one page to another.

Thanks for the Clear and concise responses to my question. It works great and I am able to support my users better now.
Lowell

Have you tried……

In reply to total from one page to another.

Have you tried putting a formula in the cell of the sheet
where you’d like the total to appear? E.g. The total
exists on sheet 1 in cell A25. You’d like to carry it over
to cell B1 sheet 2. In this new cell (sheet2:B1), type the
equal sign to start the formula, and then simply click on
the tab for sheet 1, and click in cell A25 where the total
appears, and press return to end the formula. You are
returned to sheet 2 and the total will be in top cell of the
second column. Byusing this method, if the total in cell
sheet1!A25 changes, the total in cell sheet2!B1 will
dynamically update.

LTBREH – Total from one page to another

In reply to total from one page to another.

Click the cell you want to place the total in and type and equal sign (=). (Example: Sheet2, Cell A1)

Click the sheet tab (Example: Sheet1)for the sheet that contains the total. Next, click the cell (Example: Cell A20)on the sheet that contains the total and press the enter key.
You should see the total on Sheet2,in Cell A1.

The formula in the formula bar for Sheet2, Cell A1 should look like: =Sheet1!A20

Get total in new sheet

In reply to total from one page to another.

Here’s an example to get total from one column in one sheet of a workbook to another sheet in the same work book.

Example: You want to get the total of C25 on sheet 1 to A1 in sheet two.
select A1 in sheet 2, type =, then select sheet 1, click cell C25 and press enter. This will make a relative reference to cell C25 of sheet one in cell A1 of sheet 2. To make the reference absolute, double click cell A1 of sheet 2 and insert “$” in front of C & 25 ($C$25).

Total from One page to another

In reply to total from one page to another.

the format is entered into the desired box as follows:
= ‘ worksheet name ‘ ! ColumnInitial RowNr

example =’1stQuarter’! A33.
I’ve used this formula to summarize scout advancements for our district for the last 4 years.

total from one page to another

In reply to total from one page to another.

use the “=” function
In the beginning cell of the new worksheet type
=sheet!cell name from the cell on the previous page example: you have a number in cell D5 on sheet1 and you want it in cell A1 on sheet2 In A1 on sheet2 the formula would be =Sheet1!D5

Formula

In reply to total from one page to another.

to “transfer” data from one cell to another, use the formula “=”.

Lets say colmuns: a b c d e f
1 2 4 3 1 1
2 5 1 4 2 1
3 ___________________
4 7 5 7 3 2 = 24
Row 4 would be formulas..i.e. =a1:a2
Cell f4 would be a formula…i.e. =a4:e4

If you want f4 to be “copied” into a cell on Sheet2, then go to Sheet2, select the input cell and enter the formula “=”, then bounce back to sheet1 and select the cell you want and either depress the enter key or the check mark up top by the formula bar.

If you can figure the syntax, you could type it in direct…..something like ….=Sheet!f4.

The advantage with this method vs. a straight value entry is that the sheet2 figure will never have to updated when the sheet1 figure changes.

Good luck.

Spooling job name

In reply to Excel-specific questions

I have a macro that sends out copies=$C$3 of an sheet with a reference number.

But when I have several dozens of different printing jobs but with the same name… its very dificult to pause or cancel specific job!

I need to add an small string to the printing job name within the macro.

Thanks

PRINTING ONE DOCUMENT on TWO PRINTERS

In reply to Office suite Q&A II

Is there any way you can print one document to two printers. What I want to do is print the first document on my color printer and four copies on my b&W printer

Print twice click once

In reply to PRINTING ONE DOCUMENT on TWO PRINTERS

If you record the process, as you normally do it, by turning on your macro recorder in Word. Then add a button to the toolbar and assign the macro to it. All you will have to do is click once and print twice.

newsletter address and outlook rules

In reply to Office suite Q&A II

as the sender address of the newsletter varies with each weeks edition, as does the subject, I can’t find a way to set up a rule to automatically forward it. Anyone have any thoughts on this – or alternatively , can Techrepublic put some unique words in the subject which I can use for the rule.

More info please…

In reply to newsletter address and outlook rules

Which newsletter are you referencing? I have an Outlook rule that forwards all my TechRepublic emails from home to work, based on finding “TechRepublic” in the subject line.

You must be talking about a different newsletter. Don’t forget that you can create a rule based on finding specific words in the email’s body as well, so that may give you another option (if you’re using Outlook).

MS Access application background

In reply to Office suite Q&A II

Hi people!
This is realy not a problem I had, is only a curiosity. Anybody know any method to change the Microsoft Access background? (color or inserting a image, etc).
And I mean only Access, I know we can change an application background color changing the settings using a Display Properties and then Appearance Tab.
But, in Access only?
Thanks

MS Access application background

In reply to MS Access application background

Come on, people!, What happen?. This mean that nobody have interest in my suggestion?. I don’t think so or may be I don’t explain myself clearly?
I only know that everytime I make an application in Access, for myself or for any client, I will like to have the possibility to change Access background and convert it to something better on the eyes where my forms and reports look more attractive. For example, inserting a Company picture or tiled image.
I try creating a form with this setting and make it open maximazed everytime application open, but in that case, all forms will maximaze too and that wont work fine for me.
Any suggestions?

Is this what you need?

In reply to MS Access application background

When you design a form, you can certainly add a picture to the background, change the colours and so on. In the design view, click in the very top left box in the view and open the Properties page. There you have the option of adding pictures (type “add a picture” into the help box and it will give you the details)

Changing background colours is, I think, only possible in the various sections of a form – just right click on the background in design view, select properties and make your choice.

No DBlayney, is not that.

In reply to Is this what you need?

I mean change a Microsoft Access background, which is normally gray color. The background where open forms, reports, querys, etc. In that way we can have a completelly customized application made in Access, once we have the applications itself, Menu bars we created, Help file we made for application, etc. What’s remain to change? Only Microsoft Access gray background.
That’s what I mean.

[Author]

Replies