I'm at my parent's house, and they're running Windows XP and Word 2002. They have four users set up on the computer. As a consequences, there are templates everywhere...
Somehow, they fragged the default template on Word, and now when you open up word, it has some formatting they don't want (like a font change, and it is a two column document).
Where do I go to chase this thing down? I just want Times New Roman, 12 point font, no columns.
Thanks in advance!
I have two lists with identical headers.
Stock Price Yield
the first list is my master list of information.
The second list is new entries.
I want to be able to combine the two lists, but only have one line for each stock... if there is a stock that shows up in the master list AND the new list, the values (for price & yield) in the new list should take precedence.
I'm not sure where to start on this one... if it's a matter of writing a macro, or some type of advanced data filter, but I'm pretty sure it can be done.
Thanks in advance!
[EDIT] Thanks, Derk, it's not quite what I was looking for, but it works.
I'm building a pivot table, and hitting a wall.
I have one column of data labled "Status." I need to be able to have rows of my pivot table that summarize only certain codes.
For example, the possible values of "Status" are L, B, and C.
I want a row in my pivot table that is just a sum of all the Ls, another row for Bs, and a third row for Cs.
I thought maybe I could do this with a custom formula, but I have no idea how. I guessed a few things, but nothing is actually returning a sum.
Table Sample:
Month______Status______Closed
Jan__________L__________Feb
Jan__________B__________Open
Jan__________L__________Jan
Feb__________C_________Feb
Feb__________L_________Feb
My pivot table should look like this:
Month______Jan______Feb
Total________3_______2
L___________2_______1
B___________1_______0
C___________0_______1
If that's too confusing, I'll upload a sample when I get home (the firewall at work blocks it).
Those are some nice solutions! Thanks to all... I'm going to hit the books on UDFs to see if I can learn more about how this works so I can do more on my own... I'm thinking a UDF that pulls the 1st, 2nd, 3rd, 4th, or 5th string would be cool. Thanks for the nudge in the right direction! And Andy - your solution is immediately useable without me hitting the books hard. Gracias! :wink1:
That's a similar mechanism to what I'm using... the problem is I need to be able to start to finish return what place the 4th space is in without using multiple cells.
So far, I'm up to this formula:
=FIND(" ",B1,FIND(" ",B1,FIND(" ",B1,FIND(" ",B1)+1)+1)+1)
I was just hoping there was something a little cleaner... as I'm using nested versions of that formula combined with LEFTs, RIGHTs, and LENs to pull out the strings that are delineated by those spaces.
What's fun is my file actually uses more than one space between fields, so it actually has TRIMs mixed throughout the whole thing. My formulae are getting really LONG and confusing. :o I was hoping there was a shortcut I didn't know about.
Ah, well, I'm slowly getting it to work!
Example (pulling third text string):
=RIGHT(LEFT(TRIM(B1),FIND(" ",TRIM(B1),FIND(" ",TRIM(B1),FIND(" ",TRIM(B1))+1)+1)),FIND(" ",TRIM(B1),FIND(" ",TRIM(B1),FIND(" ",TRIM(B1))+1)+1)-(FIND(" ",TRIM(B1),FIND(" ",TRIM(B1))+1)))
Thanks for your help!
I have a text string with four spaces in it. I can find the location of the first space easy enough.
=FIND(" ",B1) (returns where the first space is)
Second space is a little trickier, but I figured out a way...
=FIND(" ",B1)+FIND(" ",RIGHT(B1,LEN(B1)-FIND(" ",B1)))
I'm hoping there's an easier way that I don't know of... because taking this to the 3rd & 4th space is gonna be really ugly.
Any ideas?
tagging this post for future self education...
Cool! What other impacts does this have? Are there other features that will be disabled?
A1: Calls In
B1: Calls Out
C1: Faxes
D1: Hours Worked
E1: Total Contacts
E2: =SUM(A2:C2)
When I enter a value in A2, sum shows that value.
When I enter a value in B2, sum adds it in.
When I enter a value in C2, sum adds it to the total.
When I enter a value in D2, the SUM formula changes to =SUM(A2:D2).
Arrrrrrrrgh!!!!! Why is this happening!!!!!
bump
Is anyone aware of a way to suspend calculating pivot tables?
I can't hide rows, unfortunately, as I have 3 pivot tables side by side that all must be printed.
Wow, I was way off base. Nice code, Derk.
Is there anyway to suspend calculating the fields while the For / Next loop runs? I tried putting these lines in, but the macro still calculates the pivot table after each value of p.
Application.Calculation = xlManual
With ActiveSheet.PivotTables("Tardy").PivotFields("Date")
On Error Resume Next
For Each p In .PivotItems
p.Visible = DateValue(p.Name) > Now - 90
Next p
End With
Application.Calculation = xlAutomatic
Ok - looking at just my date example, I want to uncheck dates older than 90 days. I decided to set five cells with formulae to return 90, 91, 92, 93, and 94 days old (=Today()-90, =Today()-91, etc). The macro should hide each of them. I started by declaring five variants, then setting them to the values found in those cells. Where I get stuck is hiding the values.
Dim ninety0, ninety1, ninety2, ninety3, ninety4
Sub Macro1()
ninety0 = Sheets("Report Card").Range("AK1").Value
ninety1 = Sheets("Report Card").Range("AK2").Value
ninety2 = Sheets("Report Card").Range("AK3").Value
ninety3 = Sheets("Report Card").Range("AK4").Value
ninety4 = Sheets("Report Card").Range("AK5").Value
With ActiveSheet.PivotTables("Tardy").PivotFields("Date")
.PivotItems(I GET STUCK HERE).Visible = False
End With
End Sub
I've tried putting my variable names there (where I'm getting stuck), but it isn't doing it. I feel like I'm close, but just not quite there.
I have a couple of Pivot Tables that I'd like to show rows that meet changing criteria.
I know how to go in and manually check whether or not a specific date is going to show up, but is there a way to uncheck any dates older than 90 days?
Something like,
If ("Date") < Today()-90 Then Uncheck selection
Another pivot table I'd like to do this on is if the row is less than 30%, don't show that row.
Thanks, gentlemen, working like a charm. 
I'd like the macro to name a region. Whenever the macro is run, it will start in cell B9, select the current region (list), and then define that range as a named region. When I attempt to record this macro, it records the specific cells that the current list occupies. The problem with this method is that sometimes the list will be larger or smaller, so I need the macro to be based off the CurrentRegion (as selected in step two below).
What can I use besides "RefersToR1C1:="='Individual Pivots'!R8C2:R11C5" to accomplish this?
Current code:
Range("B9").Select
Selection.CurrentRegion.Select
ActiveWorkbook.Names.Add Name:="namedregion", RefersToR1C1:= _
"='Individual Pivots'!R8C2:R11C5"
Derk, you are the man!
Works like a charm. :biggrin:
Time to bump this post... I was called out of town, but now I'm back to trying to solve this.
Most excellent - and now that it clicked I see where you already told me that. I'm catching on! 
At this point I'm 90% good to go. For the report I really DO need the pivot table. My task:
Select cell A9
Start Loop
If activecell = "", end macro.
Make name field on PivotTable4 = selected cell
Print
Arrow Down one cell
End Loop
I've never used advanced filters - I can see some nice benefits there. Thanks, Derk.
Running the macro, I get the following:
Run-time error '1004':
Method 'Range' of object '_Global' failed.
Shooting in the dark, I tried naming a range "Extract", but that did not seem to fix it.
I can get the following to work:
Range("data").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range( _ "Criteria"), Unique:=FalseMy solution just filters the data base. It looks like your method attempts to put the data in a separate location, and I like that. Any suggestions?