Is there functionality in Excel 2010 to convert a cell that has last name, first name to first name last name? For instance:
Before: Mouse, MickeyAfter: Mickey Mouse
I'm trying to merge data from 2 sources, and the common data between the two is the name. However, it is formatted differently in each data source.
is there a way to change the legend keys (aesthetically) in 2010?
I created a pie chart using a template but I don't want to keep the defualt legend keys. I tried changing the theme but it appears that the legend keys that come up are the same no matter what theme I change it to.
The Chart is a 3d Pie chart, I think that matters because the regular square legend keys show when the chart isn't 3D.
Excel 2010
I have this code in a macro, the range will change as more data is added. so that I dont have to keep changing the range. How can I have this code autofill from the activecell to the last cell that has data in column M.
Selection.AutoFill Destination:=ActiveCell.Range("A1:A50000")
ActiveCell.Range("A1:A50000").Select
I'm using excel 2010, looking to import a .xls file to a tab from a web query. Herein lies the problem: the .xls file is named based on the current date. So for instance, xLfile03.05.14.xls. Which changes the next day to xLfile03.06.14.xls. This is not a local file but one on microsoft sharepoint. It's ONLY available through sharepoint.
View 1 Replies View RelatedI am working with a 2010 Excel spread sheet and need some direction.
One column has dates in it. Some dates are like dd/mm/yyyy (eg 15/03/1974) and others in the same column are in the following format 14th October 1983.
I need all of the dates to be in the first format (dd/mm/yyyy).
Is there a way to do this without manually changing each field? I have already tried highlighting the column, then clicking the 'numbers' arrow and picking 'date' from the number tab but that didn't work. It's never that easy, is it?
I am using Excel 2010 and trying to change/edit the color of the tabs in my workbook to turn green or red based on a y or n placed in a cell (the same cell on each tab). I have tried variations on several themes others have asked about as well for Excel 2007 and attempted to adapt them to fit my situation but none seem to work. Here's what I was starting with:
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Value
Case Is < 2.5
Sheet1.Tab.Color = vbRed
Case Is > 2. 5, Is < 4
Sheet1.Tab.Color = vbGreen
End Select
End Sub
The cell I'm using is F2 and my cell value is simply y or n. I realize the example above references numeric values and greater than/less than options, but I'm not sure how to correct this for my need.
When I place a text box in Excel 2010, the default is for everything to be locked (Box is locked AND text is locked). I want to change the default setting so that when I start Excel fresh, anytime I place a text box the "LOCK TEXT" box is UNCHECKED.
There's a place where you can select "Set as default text box") after you make the changes, but it's not sticking (even in the same document without closing. IE, I make the changes to a text box, then click the option that says "Set as default text box", place a NEW text box immediately in the same document but the text is still locked.
I was recently upgraded from Excel 2003 to 2010. I have some code that creates a 3d clustered bar chart, previously the chart's rotation was 0 and now it seems like Excel wants it to default to 15 degrees. I'd like to change the rotation back to zero, but Excel doesn't seem to see the chart.
I recorded a macro, inserted the relevant recorded code in my routine, but Excel doesn't seem to like it.
' begin code ------------------------------------------------
Dim Rng As Range
Dim cTyp As XlChartType
Set Rng = Range(Cells(2, 1), Cells(11, 2))
cTyp = xl3DBarClustered
Charts.Add
[Code] ....
I'm getting a Visual Basic error that says "Run-time error '-2147024809 (80070057)': The item with the specified name wasn't found."
Excel seems to know the chart name, but doesn't seem to think there are any shapes (initially, a loop looking at the names of shapes in the shapes collection returned nothing; printing ActiveSheet.Shapes.Count returns zero.)
What is the correct method of rotating a 3d clustered bar chart?
I still get the 'Object doesn't support this property or method. Error code 438' error. I am using Excel 2010
VB:
j = 3
Do Until j = 6
ActiveWorkbook.Sheets(k).Activate
Set chartX = ActiveSheet.ChartObjects("X")
[Code]....
The macro is supposed to dynamicaly adjust series range for 3 charts for x,y,z, values in each of sheets. Charts are a line type.
My office recently upgraded to Office 2010 and we would like (in the accounting department that I work in) to change the default number formating in a blank sheet to Number, 0 Decimals, using seperators, from the current default of general format number.
I have looked for the Book.xltx file to replace but can't see it any where.
I have a user here at my company that is having a strange issue with Excel. When she moves a page break in her document, Excel freezes up, then once it finally makes the change (if it doesn't crash), some (but not all) of the images that are in the document resize to super small.
For instance, she may have 50 rows. Each row contains a column with an image, then a few other columns with product information. Changing a page break may cause ten of the images to become tiny for no apparent reason. Resetting page breaks seems to cause the document to explode, with cells being thrown all over the page into different locations and columns becoming uneven.
When I make the same change on the same document on my system (both using similar specs and Office 2010), this does not happen.
When a change is made in cell L11, cell N11 should be locked automatically for typing. This is true for the cells between L11:L25, and cells N11:N25.
I have been trying to come up with a code that will:
1 - start my macro when f.ex. L11 is > 0
2 - lock N11 when L11 is filled out
3 - unlock N11 when the input in L11 is deleted
This is my attempt so far to put together a code:
[Code] .....
I'm using Excel 2010.
We have just refreshed our entire printer fleet to Xerox printers and as part of the project we have selected all defaults to B&W and duplex.
On a multi sheet excel file, If we click print then choose entire workbook and then change the print options to colour, it prints the only first worksheet in colour, and all the others in mono. i have read that excel treats each worksheet separately and does not cascade the changes made made in the first worksheet even if all sheets have been selected.
How can I get them all to print in colour?
We can set up another printer and change the defauts to colour but we dont want to go down that road and have 2 printers installed for each model.
Is there any code out there can will/ can change the properties of the first worksheet and cascade this throughout the workbook.
We use XP and W7 workstations and office 2003, 2007 and 2010. By the end of the year all workstations will be W7 and Office 2010.
I am using Excel 2010.
BTES worksheet:
acct# Oct-13 Nov-13 Dec-13
13245 850
12458 850
45864
12385 1500
MainList - F2= (current month)
Based on the current month (which I planned on typing in) on the MainList worksheet I need to count how many blank cells are in the table above. The information above is on a separate worksheet but I can change that in the formula. Every month gets a new column.
This is what I have but its not working correctly: =SUMPRODUCT(--(BTES!AD1:BQ18=F2),--(BTES!AD1:BQ18" "))
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
Here is my broken function:
=OFFSET('Map Point'!A1048572,MATCH("Company Name",'Sheet Name'!C1048572:C595)-COUNTIF('Sheet Name'!C1048572:C595,"Company Name"),0,COUNTIF('Sheet Name'!C1048572:C595,"Company Name"))
I work with a team of users that are continually publishing reports in Word that contain charts and graphs copy and pasted from Excel into Word 2010.
We have a custom script that leverages a PDF engine to automatically convert .doc files to .PDF files that we distribute electronically to our clients. This all works great, but only if all my users select 'paste special' and Enhanced Metafile Format when adding their excel charts into our reports. Most of these people aren't tech savvy, and I'm havin ga hell of a time getting them to follow this workflow and am hoping there's a way in Office 2010 to select the default paste from excel into word when the content is a chart.
It seems like the default paste from excel is an embedded chart/graph that you can then further manipulate each component of the chart in Word; the default doesnt' paste an actual image. I am assuming the pdf renderer is using a lower resolution .PNG version of the image and when these are scaled for print and or pdf, they look like crap.
Is htere anyway I can automatically change the default paste format for the chart from excel into word to be an EMF/EMV (enhanced metafile?) Either thorugh the registry or some other saveable setting?
I have a set of four pivot tables on a sheet that I need to programmatically change a Report Filter (Page Field) so I can create sets of reports in an automated fashion. This will be the first step in that process. The change will involve choosing > 1 Role each time the code loops through based on Named Ranges I've defined that are associated with that Role.
My code thus far:
Code:
Sub TestCode()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
[Code]....
emm_dc_gsr is one of many Named Ranges that will contain a variable number of elements. Just using the one right now to see if I can get the code to work, I'll eventually make another Named Range/Array of all them so I can loop through each Report ("ReportPick").
I want the Report Filter to consult that Named Range for its values and apply those values to PivotField "Role" that is used as a Report Filter.
When running this code above, I get a "Role" Field that says "All" but no values (the table is completely blank), with no evidence as to why it'd be blank (all filters in every Report, Column and Row are working normally and are filled in). When I choose a value manually after the code is run, the pivot table values populate. Do I need to somehow index the Named Range in that loop? I'm just confused about this step right here:
For Each pi In pf.PivotItems
If pi.Value = RolePick Then
pi.Visible = True
Else: pi.Value = False
When I've run other versions of the code, I've gotten an array version of it to "work" using LBound and UBound, but it never chooses the right two values even though those are verified as stored in the array via a pass-through. It chooses the first few values in the Report Filter.
Here's the corresponding code for that:
For i = LBound(myArray) To UBound(myArray)
pf.PivotItems(i).Name = myArray(i, 1).Value
pf.PivotItems(i).Visible = True
Next
I do not care if I use an array or a Named Range. I just want something that is simple and works. Passing the values directly from the named range seems easiest to my brain, but I'm open to anything and I'm clearly missing something (probably silly).
I also have no idea why " .AutoSort xlManual, .SourceName, .EnableMultiplePageItems" is necessary though every piece of sample code I've seen seems to have some variation of it.
(Using Excel 2010, Windows 7.)
I would like to accomplish 2 things in my Excel 2010 spreadsheet by click a cell which already has a number and formatting in it.
1. How do I change the color of the cell, the color of the number in it, and the border around it. (Make it look like I just pressed a button by clicking it.)
2. At the same time have the text in different cell and the number in the selected cell appear in another different cell. (Text in a different cell = A , and the number in the selected cell = 23, the value in the resulting cell be "A 23") Everything I would like to happen at the same time by clicking the selected cell. I would also like this to be done several times by clicking different cells and not changing the previously selected cells.
How, via VBA, would I add a comment from a userform (text box name: txtReason) into a cell that may already have a comment in it? I would like to keep the comment that is in the cell and then have the program add a "/" and the next comment from txtReason..
(using excel 2010)
I am trying to send bulk emails from my excel 2010 - however I am getting a POP UP. find the screen shot in the enclosed word document So every time a new mail is sent from excel we need to press the button allow Is there a way where I can turn off this warning.
View 6 Replies View RelatedI have created a macro in excel 2010 which enable the file to save (extract) data into separate location and name. The vba code for macro is as follows: Question: How can I save this workbook with reference to the value containing in cell B2? (it is named temporary now - as defined in the code)
Sub aaa()
'
' aaa Macro
'
[Code].....
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest()
Dim n
Dim V
Range("A1").Select
V = ActiveCell.Value
[Code] ........
I am trying to adjust the below macro so that it will work in Excel 2010.
Sub OpenAndProcess()
Dim fs As FileSearch
Dim I As Integer
[Code]....
I have a worksheet full of names in column A that are "First Name Last Name", is there a VBA code that I can write so when a user inputs the name in the "First Name Last Name" format it automatically changes the format of the name to "Last Name, First Name"?
View 3 Replies View RelatedWhen I tried to import data in a CSV file, I ran into a couple of issues when trying to change the date format from MM/DD/YYY to other date format. I've already tried a couple things to no avail:
1. Use text to column functions and change the format from text to MMDDY
2. Use format cell function and change the date format
3. Use the text function (text(Cell,"MM-YY"))
Interestingly when I click on the cell and put my cursor and press backspace, the format will change. It seems like there's an extra space, but using the trim function does not work. I can't do this to thousands of data manually.
Is it possible to make a worksheet smaller. What I want to do is to be able to tab from, a1 to b1 to c1 to d1 etc up to g1 and then if I tab again it goes to a2. Is this possible? Is there any way of making it skip the a coloumn when tabbing even if it has data in it?
View 10 Replies View RelatedI am trying to automate the status date on worksheet so that if ANY data is changed/ added/ deleted within a range (cells "B6:L34"), the status date will insert today's date in Cell "S6" but if the worksheet is open and no changes occur within that range, the date remains the same. I used this code below and it is not working.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R1 As Range
Dim R2 As Range
Dim InRange As Boolean
Set R1 = Range("S6")
Set R2 = Range("B6:L34")
[Code] ....
I am having a problem updating a jpg file in an existing Excel file which I created. There is a LOGO in the right hand side Header block which I can delete ( the words &(Picture) appear after the logo is deleted. For some reaon I cannot replace this LOGO with a newer version. The new LOGO appears in the body text area, bot it will not drag into the header. I must have missed something very basic!!
View 4 Replies View Relatedin the date fields excel only recognises some of them as date, so when I try to change the format of dates (mm/dd/yyyy to dd/mm/yyyy) it only changes some of them so I end up with some wrong dates.how I can make excel read them all as date?
View 3 Replies View RelatedI am using Excel 2007 and I would like to move one tab to right with "Ctrl+Tab" and move one tab to the left with "Ctrl+Shift+Tab".
Macros will not let me assign "Ctrl+Shift" keys (that I could see). How can I accomplish this?
I am trying to use these exact keys because my mouse is setup so that leaning the scroll wheel left or right moves left or right in my tabbed applications by executing a keyboard shortcut. This works in all my applications except excel where the shortcut keys are different.