Posts by Jas

    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:

    My pivot table should look like this:

    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?

    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!!!!!

    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.

    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:

    ActiveWorkbook.Names.Add Name:="namedregion", RefersToR1C1:= _
    "='Individual Pivots'!R8C2:R11C5"

    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
    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:=False

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