Office Forum - Answered Threads, Office 2010

Conditional format in column A based on the value in column B.

Wed, 16 May 2012 14:28:47 Z

Hello,

I think this might be quite easy!

Id like to conditional format rows in column A based on the text in the corresponding row in coulmn B. For example if B is yes the highlight the same row in A. So John and Dave would be highlighted.

John Yes

Mike No

Dave Yes

Ed No

many thanks

move connector text position

Wed, 16 May 2012 13:17:54 Z

I need to label my connectors, but my problem is that I cannot seem to move the position of the text box on the line so the label for one is overlapping that of the adjacent line. Is there a way to fix this, besides moving the lines further apart?

.xlsm macro code stopped working

Wed, 16 May 2012 11:08:43 Z

I have an .xlsm file that all of a sudden stopped working.

The code is:

Private Sub Workbook_Open()

Set r = Sheets("ORDER").Range("J3")

r.Value = r.Value + 1

End Sub

It is suppose to change a number everytime the workbook is opened.

My cells do not add up: For example P11 has 8.00 and R11 has 48, the sum should be 384 but it comes up with 383.76. Any idea why and what I can do to stop this happening.

Wed, 16 May 2012 08:59:29 Z

1.89

0.00

0.38

2.27

2.84

0.00

5.16

0.00

8.00

383.76

5.73

Allow only one cell to be highlighted in stead of two

Wed, 16 May 2012 08:57:04 Z

When I highlight a single cell multiple cells light up. That means I deleat all or add to more than one place and formulas have more than one cell. How do I get it to light up only one?

Microsoft Word 2010 - How can this merged document be only 65,520 KB in size?

Wed, 16 May 2012 03:38:18 Z

Hello, to whom it may concern. After many weeks of trying, I finally merged 2 Office documents. Document A was a Microsoft Word 97-2003 Document of 515,008 KB. Document B was also a Microsoft Word 97-2003 Document of 206,782 KB. Both documents were converted and editing work had to be done on document B to make it the same as document A, but it finally worked. The combined document is is now a Microsoft Word Document but only 65,520 KB in size. How can this be? I used the trial 2010 Microsoft Word Office program. What was used before was Microsoft Office 2003. Microsoft Office 2003 would get to a certain point in size and then you could not save the file, hence I had to break it up into two different files. Obviously, Office 2010 can handle large sized documents with photos, word art, different fonts etc.

Thanks for any explanation, James

When I open an Excel file from Explorer by clicking on the file, two windows open: the spreadsheet AND a second Excel window, no spreadsheet open--just a blank Excel Window

Wed, 16 May 2012 01:13:08 Z

(Excel 2010, Windows 7, 64bit). When I open an existing Excel file from Explorer by clicking on the file, two windows open: the spreadsheet AND a second Microsoft Excel window (no spreadsheet open--just a blank Excel Window).

When I open the Excel program directly from within Excel, I get the single window (Book1). But opening Excel from a previously created file always gets me two windows.

How to Control the Quick Style Gallery?

Tue, 15 May 2012 22:20:04 Z

I know that I can add any of my styles to the QS Gallery and also remove any from the Gallery one at a time. And I usually keep enough control over the styles so that the menu of styles includes only those I watn to use. But the QS Gallery seems stuffed with ersatz styles, i.e., ones I don't want and in most cases have no idea what they are about. E.g., Subtle Emphasis, Emphasis, Intense Emphasis, How about Strong? Subtle Reference? Intense Reference? I can keep these unwanted styles off the menu but they take up space on the toolbar. Or is it Ribbon? Is there a way to get rid of these in the QS Gallery

How do I enter a variable with a subscript raised to a power?

Tue, 15 May 2012 21:23:17 Z

In the equation editor in Microsoft Office 2010, is there a way to enter a variable with a subscript raised to a power. If I use the script template that has both a subscript and superscript. It will only allow me to enter either a superscript or a subscript but not both.

Is there a way to enter:

\sigma_y^2

Unmatch query

Tue, 15 May 2012 20:21:46 Z

I created a match query on my table that finds all records where the Route and Miles match and for display I included StreetNumber and StreetName. This query is called qryMatchingMilesRoutes.

Based on that query I created another match query (qryMatchingStreetRoute) that finds all records where StreetNumber and Routes match. Those work fine, but when I do an unmatch query between the 2 queries nothing is displayed. What I expect to see is those records on qryMatchingMilesRoutes that were not include in the results of qryMatchingStreetRoute.

When I tried to download BCM I got a reply that I am not eligible.

Tue, 15 May 2012 19:53:30 Z

BCM

I have Office Professional Plus 2010 and can't figure out how to get BCM. When I tried to download it I got a reply that I am not eligible. Where do I get a download for it?

[Word 2010 Equation] How can I align equation at symbols other than "=" like in OneNote?

Tue, 15 May 2012 18:59:29 Z

(I can align using array or matrix, but it's cumbersome comparing to the method in OneNote)

In OneNote 2010 I can align at any symbol using "&"
Like typing these in equation

     a&=b
     asdf&->asdf
     blah blah&>blah

will result

                     a = b
              asdf -> asdf
     blah blah > blah

(align at =, ->, > repectively)

Can I do something like that or similar in Word 2010

Thank you.

combine formula

Tue, 15 May 2012 18:03:32 Z

Hello

I'm trying to combine 2 formulas into 1

I presently have extra columns in the woksheet and one column with the final result

Scenario

Column L Total Hours 174:00:00

Column M training 1:00

Column N consulting 2:00

Column O formula is column M less column N = 171:00:00

Column P time on phone 8:00:00

Column q available time = 13:00:00

Column R formula column P plus column Q 21:00:00

Colum S formula is column r/ column n - 12:28%

Is there a way to combine these 2 formulas into one.

I have tried several combinations but I get the wrong answer .

Thanks

Upgrading Office 2000 to Office 2010

Tue, 15 May 2012 17:58:16 Z

I currently run Office 2000 Standard on a PC with Windows XP, Home Edition, Version 2002, Service Pack 3. As Outlook, in particular, is geting a bit clunky, and Office 2000 is no longer supported, I have bought Office 2010, Home and Business. I wish to preserve the existing e-mails, contacts and Calendar entries in the 2010 version, but the instructions offer no guidance on this, particularly as I understand that Office 2003 and later use a different file type to store these data. Would you please offer some guidance, including whether I should uninstall Office 2000 before attempting toinstall Office 2010? Thanks in anticipation.

Pete

Setting variable as "This Sheet"

Tue, 15 May 2012 17:54:10 Z

Hello all,

Firstly, I apologize. I am an extreme novice when it comes to VBA for excel. I am researching, and learning, but still have a long way to go. So please bear with me.

I am currently on a project, and have stumbled onto a small problem. I have a worksheet that will contain many sheets, easily over 100 when completed. The 1 sheet (Called “Roster”) contains a list of classes, and contains a list of employees. Next to each class is a button, this button take the user to a sheet that contains information about the class. On the sheet is a button that will take the user back to the Roster sheet.

My problem occurs with my personal OCD issues :P I cant stand the site of so many sheets cluttering the bottom of my worksheet. So to remedy this issue, I have made all the sheets other than Roster, not visible. The button that takes the user to the class sheets is as follows:

(TM81 is the “class code” for one of the classes. It’s a much shorter name than the actual class name. Each class has a unique “class code”)

Sheets("TM81").Visible = True

Sheets("TM81").Select

Sheets("Class Roster").Visible = False

This works fine, it takes the user to the class’ sheet, and makes the roster sheet not visible. Exactly what I want.

The problem arises in my button that returns the user to the Roster. I can make it so the Roster sheet becomes visible, and the user is taken there. I however don’t know the proper “verbage” to make the original sheet not visible. I can manually put each previous sheets name in the code to make it not visible again. This will be extremely tedious as there are potentially many, many classes. This is what I currently would have to do for every class page individually.

Sheets("Class Roster").Visible = True

Sheets("Class roster").Select

Sheets("TM81").Visible = False

I don’t want to have to type this for every class. My idea would be something as follows:

DIM CurrentSheet AS (im not sure what a “sheet” is stored in)

(I forget the proper code for the following, sorry)

Then set CurrentSheet as (Some kind of “This Sheet” function to set this variable to equal the sheet that the user is currently viewing)

Sheets(“Class Roster”).Visible = True

Sheets(“Class Roster”).Select

Sheets(The variable “CurrentSheet”).Visible = False

The idea here is to make a bit of code that I can set as the macro to a button on each of the class sheets, instead of having to write a new line for each one with that sheets name in it.

Thanks for reading, and any help available.

how to select all text and copy text to new document

Tue, 15 May 2012 16:31:18 Z

How do I select all text and copy text to new location such as copying my resume from word 10 to career builder? In xp verison I could select all and copy but word 10 will not work that way for me.

I will be getting a new computer. Can I transfer the software on my current one to the new one

Tue, 15 May 2012 16:05:51 Z

I have the Student version of Microsoft Office installed on my current computer. I will be getting a new one. If Microsoft Office is not preinstalled, can I transfer my activated copy to a new machine.

Thank you

Show the style area in Word 2007/2010

Tue, 15 May 2012 15:45:07 Z

Word versions till 2003 had a very nice feature which allowed you to show a style margin on the left of the text (Tools | Options || View ||| Style area width). Does this feature still exist in Word 2010? - if so, how do you enable it? Yes I know that the styles are visible on the Home ribbon, but this means that you have to move your eyes from the paragraph you are reading up to the ribbon and back again. So much less eye strain when these are side by side.

Also, clicking against the style in the style margin selected the whole paragraph. How can you do this in 2010, other than by selecting the first line then dragging down?

How can I add date to excel without using =Now()

Tue, 15 May 2012 15:40:50 Z

Whould like to have the current date inserted into cell E5 based on entering a value in G10.

Can this be done without me entering =NOW(). This causes the sheet to change dates when I reopen.

Private Sub Workbook_Open() '

     If (Range("g10").Value = "") Or (Len(Range("g10")) = "") Then
    Range("g10").Value = InputBox("Enter Unit Number: IE: 4369")
    Range("C16").Select

End If
End Sub

Don Murray

Edit an Access DB Table field

Tue, 15 May 2012 15:34:35 Z

I have a field in an access table which contains two elements separated by &. Eg XXXX & YYY

I wish to edit the field using Access Macros removing either the first or the second element and the & , leaving one (or other) of the elements in place.

Eg XXXX or YYY.

Any suggestions would be most welcome. Peter Melville Reigate Surrey England.

Can Run Macros but Not VB Code

Tue, 15 May 2012 15:14:47 Z

I'm running windows 7 and have just downloaded the trial version of Access.

I have a sample macro that simply calls the messagebox action to say "Hi" and this runs fine.

I also have a simple piece of VBA:-

Public Sub Hello()
MsgBox "Hello from VB"
End Sub

But when I try to run this from the VBA window or by calling it from a macro I receive the message:-

"Macros are disabled for this project."

I'd be very grateful if someone could explain how to enable VBA in Access. Thanks!

Can I reference a Workbook scoped named range in the Worksheet Change code?

Tue, 15 May 2012 14:04:39 Z

I had three worksheets using the Worksheet_Change event to call a procedure in Module1. Everything worked fine.

Because users need a change on 1 of the 3 sheets, the Module1 code won't work for the new layout. I thought it would be easiest to just copy the code over to the Worksheet_Change area, modify it as needed and stop calling the Module1 code. The problem I ran into is when the Worksheet_Change code attempts to reference a Named Range.

Target.Offset(0, -2).Value = Range("TODAY").Value

Right click menu do not includes to start excel

Tue, 15 May 2012 13:44:07 Z

I suddenly found when I right click on any blank part in my desktop, there is [text]/[doc]/[ppt] and so on, but no longer has the [excel], but if I open an old excel or use start-programme-microsoft office-excel to start, they are works well...

Using password but with option of Read only

Tue, 15 May 2012 12:47:42 Z

In excel 2010, when I protect the document with a password it now doesn't have an option for people to open it as read only. It used to do this but for some reason it has stopped and just asks for a password with no other options.

center titles in MS Word document

Tue, 15 May 2012 12:44:21 Z

I'm editing a document written by someone else. When I try to center and bold titles and subtitles, the entire document gets centered and bolded. I've been an editor for many years, and I've never run into this problem before. I'm using Office 2010.

How do I use excel cells in VBA to send email?

Tue, 15 May 2012 11:45:01 Z

This is the code I use to send an email from excel.

Is there a way to use info from the spreadsheet for the subject instead of typing it in.

Private Sub CommandButton1_Click()
Dim oOLook As Object
Dim oEMail As Object
'
Set oOLook = CreateObject("Outlook.Application")
oOLook.Session.Logon
Set oEMail = oOLook.CreateItem(0)
oEMail.Display

On Error Resume Next
With oEMail
.to = "P.O. Request"
' .CC = ""
' .BCC = ""
.Subject = "PO request"

.Body = "See P.O. Request as per attached."
' .Send
End With
' On Error GoTo 0
'
End Sub

Excel VBA Macro Required

Tue, 15 May 2012 10:40:39 Z

I have following data in sheet1 containing list of 06 observations (obs1 to Obs6) in Column A and list of samples (1 to 10) given in columns B to K. Only some observations relate to particulars samples. We validate all these observations to all samples and mark “Yes” in respective column, if observation exist in particular sample. I want to display results in Sheet2, observation wise list of only those samples that have been marked “Yes” against each observation. For example if samples 1,3,9 of obs1 have been marked yes. Then report should show Obs1 as heading and list of samples in separate rows.

Sheet1
Sample
Observations	1	2	3	4	5	6	7	8	9	10
Obs1	Yes	Yes	Yes
Obs2	Yes
Obs3	Yes
Obs4
Obs5
Obs6	Yes	Yes
Required Results in Sheet2
Obs1
1
2
3
Obs2
5
Obs3
8
Obs6
1
6

when I click on a power point file it opens as unreadable word document (office2010)

Tue, 15 May 2012 10:35:31 Z

I have office 2010 and have been using it without a problem previously and am not aware of making any changes to either program but now to open ppt I have to save it and it has the word w on it, then i right click and open as ppt. Help

Transferring Office Software to a new computer

Tue, 15 May 2012 09:53:04 Z

Transferring Software

bought office 2010 as a download in the last year, my lap top is about to give up the ghost so i want to know if i can transfer the software to a new laptop and not have to buy it again

Filter multiple values

Tue, 15 May 2012 08:36:19 Z

Hi all,

I have been scouring the internet for a specific answer for the last hour and a half now, and it's left me more frustrated than enlightened... I'm setting up a library system in Excel to be able to quickly filter specific details of a large array of advertisements, leaflets, brochures, etc and quickly find its location (also specified in the excel sheet.

A screenshot of how it looks:

Click here

Because of the fact this list will get pretty large pretty fast, I need a good system from the start. I want to be able to filter all columns, but the problem resides within the Product Group and the Products section.

I.e. #3 in the example screenshot.

This advertisement contains both PG3 and PG4, and contains three different products. Say that in three months I want to look for this advertisement using as a criteria PG4 and 903.001, I want #4 to appear as well. Simply put:

How do I get all Product Groups to appear in one filter, and how do I do the same with Products?

If there is anything else you need to know in order to be able to help me out, just ask away, and I'll do my best to answer! Thank you very much in advance.

If this is not the optimal way of doing it, I'm open for suggestions!

Regards,

Bassianius

In a table of occations I want to count the number of certain values the last 5 hours.

Tue, 15 May 2012 07:17:11 Z

See table below. I want to count the number of 1's in column AH the last 5 hour (backward in time from 15.05.2012 05:09 ), The formula should be placed in AH1 (The answer is 4).

A	B	……………….	AH
1	Time	Temp
2	14.05.2012 21:24	89,51	0
3	14.05.2012 21:26	89,93	0
4	14.05.2012 21:35	87,99	0
5	14.05.2012 21:38	88,42	0
6	14.05.2012 21:41	88,01	1
7	14.05.2012 21:49	88,43	0
8	14.05.2012 22:02	87,44	1
9	14.05.2012 22:11	88,02	1
10	14.05.2012 22:31	85,53	0
11	14.05.2012 22:45	85,94	1
12	14.05.2012 22:55	85,03	0
13	14.05.2012 23:25	86,65	1
14	14.05.2012 23:47	85,15	0
15	15.05.2012 00:30	86,29	0
16	15.05.2012 01:02	84,74	0
17	15.05.2012 01:43	86,23	1
18	15.05.2012 01:51	85,66	0
19	15.05.2012 02:10	86,73	0
20	15.05.2012 02:36	85,27	0
21	15.05.2012 02:45	86,11	0
22	15.05.2012 02:57	85,59	1
23	15.05.2012 03:05	86,01	1
24	15.05.2012 03:26	85,04	0
25	15.05.2012 03:35	85,53	0
26	15.05.2012 03:41	84,37	0
27	15.05.2012 03:56	85,48	0
28	15.05.2012 04:00	84,77	0
29	15.05.2012 04:04	85,19	0
30	15.05.2012 04:13	84,12	1
31	15.05.2012 04:18	84,6	0
32	15.05.2012 04:20	84,18	0
33	15.05.2012 04:38	85,16	0
34	15.05.2012 04:46	84,66	0
35	15.05.2012 04:54	85,57	0
36	15.05.2012 04:57	85,16	0
37	15.05.2012 05:07	85,85	0
38	15.05.2012 05:09	85,52	0

Thanks in advance

Per Morten Lefdal

How can I type set like this

Tue, 15 May 2012 06:23:02 Z

How can I type set like this automatically

                                     asdfasdfasdfasdf                                          (1)
                            blah blah blah blah blah blah                              (2)
             Content Content Content Content Content                       (3)
                                              Example
                                      Example Example                                         (4)

Content are aligned at center and there are numbering at far right or left.

Thank you.

How to Add TextBox To A Frame Via Code?

Tue, 15 May 2012 04:26:28 Z

What would be the correct version of the following attempt to create/add a textbox within a frame on a form, upon a click of Command Button?

Private Sub CommandButton1_Click()

Me.Frame1.AddTextBox

End Sub

Thanx in advance.

Mouse pointer constantly busy after upgrading to Office 2010

Tue, 15 May 2012 02:20:16 Z

Anyone know why my mouse pointer is constantly spinning since I upgraded my Office suite?

Even when I reboot and open nothing, the cursor spins for 3-5 seconds then stops for 3-5 seconds, then repeats.

It never did this before. What could be taking these resources?

Thanks!

How can I edit an active document with a userform open?

Tue, 15 May 2012 01:41:52 Z

I notice that when you use Word's spellchecker you can click outside the form and edit text in the document itself rather than in the spellchecker form.

The spellchecker stays visible over the document and then you just click resume to go back to using the spellchecker form.

Is it possible to do something like this with a vba userform? How would I go about this?

Cheers

Josh

linking sales information from ebay to excel

Mon, 14 May 2012 22:18:30 Z

I am unsure as to whether this is possible or not. I am looking to link the sales data from ebay to my excel spreadsheet. I would want to to obtain the item name, sales price, P&P cost, date sold, ebay fee, paypal fee and listing fee and enter in to a excel spreadsheet, is this at all possible or is it wishful thinking?

Incomplete IMPORT of EXPORTED outlook.pst file

Mon, 14 May 2012 21:15:07 Z

In preparation for building my new computer I exported my personal folders to a file I named outlook.pst. My old computer had MS Office 2007. Once the build was complete I installed MS Office 2010, configured my e-mail accounts and imported the outlook.pst file. ( The export was done on 4/22 and the install of the new Outlook wass on 5/7. The old system had Windows XP 32 bit and the new has WIndows 7 Ultimate 64 bit) ALL my personal folders showed up BUT all e-mail sent and received after 11/16/2011 are missing. ALL appointments in Calendar after 12/6/2012 are missing--except that appointments coming up in August and September show up. My contacts do not include updates entered after November. Otherwise the new Outlook works fine. This was a completely new build so I can get at the outlook.pst file stored in the applications directory of my old hard drive (I'm ging to use it as a data only dirve).. I'm hoping that once I get iTunes installed and sync the iPhone my missing calendar items and contacts will show up. At worst I suppose I can reconnect the old system and simpy forward my missing e-mail items to myself so I will have them. However, I would much rather simply 'fix' whatever has gone wrong with my import of the file I created with Export. Thanks in advance.

How do I modify my 5 week rolling average formula to exclude blank cells?

Mon, 14 May 2012 20:52:51 Z

So i have a spreadsheet that calculates golf averages for a golf league based on a 5 week average, however the problem i have run into is that it is not looking past blank cells. The formula is:

=AVERAGE(OFFSET(O7,0,MATCH(-1,O7:AL7,-1)-1,1,-5))-36

What i am seeing is that it is looking at the last 5 weeks, in that one of the weeks is completely blank, and determining the average. The problem is that when a blank is present in those 5 weeks, it really is giving me a 4 week average with the blank cell screwing up the formula.

How can i correct this?

Thanks,

Payton

Uninstalling Office 2010 trial has caused Office 2007 to go missing

Mon, 14 May 2012 20:34:51 Z

Split From: "Uninstall Office 2010 Trial Version"

I wish I'd read this and some other posts earlier.

I already had Office 2007 on my computer. I did not make any changes to it before installing the Office 2010 trial. Both were on my computer.

I decided not to get Office 2010 and uninstalled the trial. Everything has been messed up ever since. I know Office 2007 is still there somewhere, because I have Word 2007 and PowerPoint 2007 pinned to my taskbar, but I no longer see them in my programs folder and can no longer click to open documents. I need to go first to PowerPoint or Word 2007 from the taskbar, open them there, and then open the document I want.

I have tried to set Word 2007 and PowerPoint 2007 as defaults for .doc and .pptx files, but they do not appear in the list of available programs from the control panel.

I tried a system restore to before I uninstalled Office 2010 but that has not solved anything (the trial has since expired so I can't use it.)

I am afraid to just uninstall both Office 2007 and the 2010 trial and then reinstall 2007 (it was preinstalled when I bought this Dell, Windows 7 64 bit, but have the installation CD, unopened.) I have several years worth of emails in Outlook and cannot bear the headache of losing them and/or needing to save and reconfigure everything.

If I had anticipated any of this I would never, ever have gotten the Office 2010 trial. I am very frustrated. Can anyone help?

En installation av Office 2003 på din dator är skadad och installationen kan inte fortsätta.

Mon, 14 May 2012 20:02:38 Z

Split From: "Error 1713 during installation of language pack in Office 2010 64-bit."

Följande meddelande kommer upp när jag försöker installera Office 2010:

"En installation av Office 2003 på din dator är skadad och installationen kan inte fortsätta. Ta bort eller reparera Office 2003-produkten och kör installationsprogrammet igen."

Vad gör man åt detta? Jag vill inte ta bort Frontpage 2003 eftersom jag använder detta program.

MVH/Henrik