Office Forum - Answered Threads

Office 2007 CD from previous PC

Wed, 16 May 2012 18:48:42 Z

I have an Office CD which came with my current PC. I am planning to upgrade/replace it. Can I use the CD to install Office 2007 on my new unit?

Best tool or approach to use

Wed, 16 May 2012 18:01:01 Z

I have a query which returns a listing of Projects, Dates, Amounts

ABC   2012-04-25    $45978
ABC 2012-04-05    $250
DEF   2012-07-11    $6372

I would like to turn this into a report, but am wondering how to tackle it. I would like the report to

1. List all the data (as show above)
2. For each month make a grouping of sort with the summation for that month

I wasn't sure it Access or exporting it to Excel was the best route.

I hope this make sense, it is very hard to merely express in words like this...

Thank you for any guidance,

Question

Blank cell being seen as "0" and calculating

Wed, 16 May 2012 15:33:15 Z

In using the formula =--(1*D10=0%) to reflect a return of 100% to objective for a "0" obtained actual against a ")" objective.

Until the "0" is actually entered in the cell for the actual obtained, there is a blank cell.

How can I make sure the blank cell reference does not calculate as 100% rather only when I add the "0", if that is what they received for that month?

The above formula is reading the blank cell as a "0" and returning a 100%. How can I stop the blank cell from being seen as a "0" and further calculating on cells that I have not gotten to yet to enter data?

I have 2 cells, but I only want one to display at a time.

Wed, 16 May 2012 15:24:11 Z

I am asking the question: -

1) What commission rate is being charged?

2) Are you being charged a fixed fee?

There is an answer box for each, but only 1 box should be populated (if they place answers in box boxes, it will provide the wrong answer).

How do I set a formula that deletes the entry in the other box, or ensures that only 1 box can be populated?

Finding Percentiles

Wed, 16 May 2012 15:07:45 Z

Hello,

I'm trying to find the 20th and 80th percentile of an array of data.

This chart is showing the counts for a spatial analysis, so ultimately the percentiles will be associated to the total counts for each objectID rather than the specific values of each column. So if the total of row 2 (ObjectID - 12) is 50000 then my percentile would be of that number.

Now what I want to do is get the 20th and 80th percentile for row 2 and 3 (There are many more rows in the actual data set) )but instead of it giving me the value of the percentile, I need the column heading (1, 2, 3, 4, 5) that the percentile is within to be shown. It would be calculated in the column after total.

Example If row one has a 20th percentile of 24000 the value calculted would come back as 2.

ObjectID | 1 | 2 | 3 | 4 | 5 | Total |

12 | 23456 | 2345 | 34523 | 3454 | 3452 | SUM |

13 | 24532 | 9856 | 12342 | 5674 | 1234 | SUM |

Any Ideas?

How do I show a minimum value of 599 in an Excel cell when the formula calculation equals less than 599

Wed, 16 May 2012 15:03:37 Z

I am trying to set a formula that will default to 599 if the answer is less than 599.

i.e.

Estate Agency commission is 1/2% on completion, but subject to a minimum of £599

Can I create a formula that will reflect a 0 actual against a 0 objebctive as 100% to objective?

Wed, 16 May 2012 14:31:33 Z

On the first three months of the year my staff has been given a "0" Objective to meet on sales in a specific area. If they obtain a "0" as an actual, how can I make sure the % to objective is reflected as 100%?

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

vba sorting by date

Wed, 16 May 2012 14:22:22 Z

I have a date column U13:U33

and an event column W13:W33

I would like some code that can sort by earliest date to latest date but not reference the sheet, just the range.

Thanks

SUMIF and Dates

Wed, 16 May 2012 14:08:07 Z

Please assume that my spreadsheet looks as follows:

A	B	C
1	203	01/12/12	01/31/12
2	14	02/01/12
3	10
4	77	02/24/12
5	-5	03/01/12
6
7	86

I am trying to write a formula in cell A7 using SUMIF. I want to sum the values in column A if there is a value in column B but not a value in column C. I have tried using ISBLANK but that doesn't work. I know this is simple but I am frustrated.

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?

Formatting cells with SelectionChange

Wed, 16 May 2012 13:07:07 Z

I have the following code that works when I click in a cell. My question is, how can I make it so when I click back out of that cell the formatting changes back?

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Intersect(Target.Cells(1, 1), Sheet6.Range("Jan")) Is Nothing Then
                With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16737792
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    End If
End Sub

.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?

Use VBA to delete sheets except defined sheets

Wed, 16 May 2012 04:46:49 Z

Dear Experts,

Some of the marcos will have "add sheet" feature.

May I know how to delete sheets except sheet1, sheet2 and sheet3.... the rest can be removed with one click ....

Thanks and regards,

Elton

Use VBA to Close all files

Wed, 16 May 2012 04:43:05 Z

Dear Expert,

Have learnt from the forum that open all files from a folder.

May I know how to close all outstanding Excel files except the file whom run this marco to close others?

Would the script to close without save and to close with save be very different?

Thanks so much for help.

Elton

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

How to convert limited text to excel spreadsheet

Wed, 16 May 2012 02:47:07 Z

I had to save an excel file as delimited text - now I want to convert it back to excel. I have done this in the past but can't remember how now.
Thank you

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?

just switched from Office Live to Office 365 and can't figure out how to update my web page

Tue, 15 May 2012 19:46:22 Z

I just switched from Office Live to Office 365 and can't figure out how to update my web page. i don't use the e-mail, would like an Excel spreadsheet in the cloud for my own use, but don't want to share anything with anyone else. I'm a one-woman business with a webpage for my customers. Nothing fancy, and I don't even know what SharePoint is! It was all so easy with Office Live......

Thanks in advance,
Laura
www.thebowwowplace.com

modify conditional formatting formula with another variable

Tue, 15 May 2012 19:25:28 Z

=AND($N9=U$7,$N9=U$7)
=AND($O9=U$7,$O9=U$7)
=AND($N9<=U$7,$O9>=U$7)

u7 date
n9 date
o9 date

i9 date
j9 date

I had assistance with the above formulas a while back and now I'm trying to modify it to NOT to format if BOTH I9 and J9 are empty.

Thanks for your help

horizontally fill adjacent cells by double-clicking auto-increment handle?

Tue, 15 May 2012 19:21:23 Z

I have a row of data in (say) B2:Z2. I replace B2 with 102 and C2 with 103. If I select B2:C2, I have an auto-increment handle at the lower right corner. I thought that I could double-click this handle to fill the entire row with 102, 103, 104, ... At least, that's how it would work if the sequence ran vertically downward. Unfortunately, I haven't found a way to get this behaviour for horizontally exending the auto-increment for a very long row. *Is* this double-click filling only available for vertical auto-incrementation?

Time Format

Tue, 15 May 2012 19:16:56 Z

I am trying to format a time with the minutes first and then the hours so I created a custom format of mm hh. No matter what the minutes portion always shows up as 1. So for instance 8:15 shows up as 01 08 when it should be 15 08. I can work around it but I was just wondering if anyone had an expanation of why it is converting the minutes to 1.

[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.

ZIP CODES on existing mappoint 2009 map

Tue, 15 May 2012 18:18:26 Z

I created a County Map in MapPoint 2009. I know this program has access to all the zip codes in the counties. How can I get the Zip Codes to show on the map? Thanks

Darin

Macro Continues to Copy to Another range and delete dupes

Tue, 15 May 2012 18:13:46 Z

I use this line to copy data from one worksheet to another. Works great.

rng is a named range on another worksheet

Application.ScreenUpdating = False
    rng.Copy Destination:=Sheets("CreateChoiceSets").Range("M" & Rows.Count).End(xlUp).Offset(1)

I use this macro on multiple named ranges and they're always 2 columns of data, differing in the number of rows.

Now, what I need to do is ALSO paste the first column of this named range to A2 of this same worksheet, AND paste the second column of this named range to E2 of this same worksheet. And I need to remove any duplicates that I might have copied over. (The duplicates DO belong when I paste with the code above, but NOT in the step of pasting into A2 and E2.

An entire macro currently is as follows, and I have about 10 different ones, which I run based on the user's button-click.

Sub AddPreps()
Dim rng As Range
Set rng = Range("addprep")
    Application.ScreenUpdating = False
    rng.Copy Destination:=Sheets("CreateChoiceSets").Range("M" & Rows.Count).End(xlUp).Offset(1)
    Application.ScreenUpdating = True
End Sub

As usual, I will grant great favors in heaven to anyone who can help. :)

Thank you thank you thank you and I swear this project is almost finished!

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.

Sum formula that ignores blank cells when 1 or more of them would have been errors

Tue, 15 May 2012 15:30:36 Z

Hello.

I would like a sum formula that adds the values of 12 cells but ignores any of those that are 'blank' (forced to be blank per ISERROR formulae). I'd like the formula to add up, say, the 11 or 10 or even 5 non-blank ones...and the # of non-blank ones could vary from day to day.

Cell E5 is a formula. It returns a value if its predecessors contain certain values, else it returns a blank cell, and I want blank cells if either of 2 conditions is met: if the cell is for a future day, or if the formula would have returned a zero. This is good because I want my external line chart #s to NOT show a bunch of zeros for future days and to NOT spike up & down if a completed day truly had zero dollars (i.e., I want the chart line to stop & start as it moves right along the axis). This part of my formula works perfectly.

=IF($A5>'Budget'!$B$2,"",IF(ISERROR('Data'!I5),"",(('Data'!I5))))

The formula in Cell E5 is copied down the rows for each workday of the month.

Then, Cell EO5 is a formula.

=E5+N5+W5+AF5+AZ5+BI5+BR5+CA5+CJ5+CS5+DB5+DK5

The formula is copied down for each workday of the month.

The problem is that if E5 is blank, then EO5 returns an error #VALUE. If N5 is blank, same thing. If BR5 and DK5 are blank, same thing.

I would like a sum formula that ignores any and all 'blank' cells and adds up the rest of the cells. Hoping someone can help. Thank you.