Posts by Relman

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Thanks for your help Guys!

    I've opted for a combination of Tom's "Switch" column and an idea from my cross-posting on Tek-Tips. That involves using MS Query to pre-filter the data using all the Custom Criteria available under AutoFilters, then making the Pivot Table from the reduced dataset.
    I'm OK using it myself, but the folks who'll be doing it for real aren't quite up to speed on all the techniques...guess I'll have to train them, eh?

    Thanks again, and keep on excelling ;)

    Hi Everyone, Happy New Year to you and Yours.

    When filtering a Pivot Table, I know you can use the field drop-downs to select various items to filter. If it's a Page field, you can choose All or any one of the items in the list.
    If it's a Row field you can Uncheck the items you don't want.

    What about if the drop down has hundreds of items, but you only want 2 or 3 of them?

    Manually Unchecking all but 2 out of 300 items in a Row Field is out of the question, really!

    It's not possible to have more than 1 Page field with the same name, which would allow you to select, say "John" or Janet" :(

    So can it be done? If so, how?

    P.S. I bet a trillion Space Credits that;
    A. I've overlooked something REALLY simple or
    B. There's a FAQ or past query on this very subject :wink2:

    Thanks for the quick replies Guys!

    Ritchie: I can't believe I'm so stupid! TVM

    Andy: Nothing happens. When I change the value of B8, B9 recalculates, but B7 stays the same: I want it to change until C9=0 :-{

    It's as tho' VBA isn't detecting the changed cell... possible? Likely? Fixable?

    The Goal Seek bit works just great on a button . . . DTH?

    Hi Guys!

    I know this is Noddy stuff, but I can't see the wood for the trees!

    Think of a worksheet.
    Put a number in B4
    Put another number in B7
    Put a number between 0.1 and 25 in B8
    Put this formula in B9:


    And this formula in C9:


    Now put this code in the worksheet's code module:

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$B$8" Then
    Range("C9").GoalSeek Goal:=0, ChangingCell:=Range("B7")

    End Sub

    Change the value in B8

    Explain why the Goal Seek doesn't. Happen that is :wink1:

    Any ideas!

    Derk: Yep, I'm sure. Yours works gorgeously as far as the dropouts go, but I need the time at zero to expand/contract along with the value of MTTR calculated in B8. Both your solution and Andy's shift the plot to the right by MTTR units, but I think that's MY fault for not calculating it properly!

    Anyhoo, both of your answers have helped me a great deal, and inspired me to try another tack. I'll have a rethink and post back ASAP.

    Andy & Derk = "Da Men"

    But... neither solution is affected by the length of time taken to do the PM, ie the value of MTTR. :(

    Parameters MART and ADLT are Mean Active Repair Time = "Spanner in hand time", and Additional Logistic Delay Time = Transit Time+Waiting for Spares+Testing, etc.

    So if you set MART to 12 and ADLT to 100, PM to 300, the first PM drop should occur at 300, stay at zero, then zoom up again at 412. The next drop should occur at 712 to 824, and so on....

    Any hope of doing that?

    Thanks for that Derk :congrats:
    I hoped that either you or Andy Pope would pick up on this!
    It looks about as close as we can get for now, and if you didn't have such a scruffy-lookin' beard I'd give ya a big kiss! :puke:

    FYI, It bends on my Excel as well :wink2:

    FYI2, I've cross-posted this on Tek-Tips, and "SkipVought" is working on it right now, so maybe he'll come up with a way to cure "The Bends" :biggrin:

    Hi Peeps! Been out of the country for a while so I’ve not monitored the site for a while. Now I’m back at work and have a problem on my first day!!

    It’s easiest to explain if you look at the attachment. This is a plot of system availability over time, where Preventive Maintenance (PM) is carried out at set intervals. It should be a sort of square wave with sloping or curved tops, the curvature of the top being varied by the value of “Beta”.

    The value (t-x) is used to reset the elapsed time to zero whenever PM is done. The formula is =MOD(Time, PM Interval).

    The column “Plotted Availability” is used to create ‘dropouts’ in the chart for the duration of the PM, which is set by the value of ‘MTTR’

    In the “Parameters” block, Yellow cells are user-entered values, Blue cells are calculated values.

    The plot doesn’t quite do it for me, because it zigs down to zero at the PM point, then zags back up, MTTR hours later. What I need is for it to drop instantly to zero at the PM Point, stay at zero for MTTR hours, then rise instantly to the value of A at PM+MTTR hours. I realise this will require 4 plotting points for each PM instead of the current 3, but I’m at a loss as to how to implement this – any clues much appreciated.

    Please see the attached .xls

    It uses the WorksheetChange() event to redimension a range [Data] as new items are added to the bottom of the list in column A, then AutoFill the formulas down column B.
    As it stands, the code works fine, but I’d like to extend the AutoFill range to allow for additional columns C, D, E….etc.
    [FillStart] is the range of cells from B1 to F1, and grows/shrinks as columns are added/deleted

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
    End If
    End Sub

    Public Sub Fill_It_Down()

    Set Data = Range([topoflist], [A65536].End(xlUp))
    Set First = [topoflist].Offset(0, 1)
    Set Fillstart = Range([First], [First].End(xlToRight))
    Coli = [Fillstart].Columns.Count
    Rowi = [Data].Rows.Count
    If Rowi > 1 Then
    [First].AutoFill Destination:=[Data].Offset(0, 1) < How to change this line???
    End If

    End Sub

    How should I specify the Destination part to allow for an expanding [FillStart] range?


    Varium et mutabile semper Excel

    Hi James,

    I tried to reproduce your Error' but it works fine for me..
    Have you got any extra quote marks or spaces in your ControlSource or RowSource property names? The only way I could get the Error was if I put "Projection!E1" (complete with quotes).



    I haven't failed, I've found 10,000 ways that don't work. (Benjamin Franklin)

    OK Pesky, I see now, but alas I cannot assist :(

    Much Luck,


    "Weaseling out of things is important to learn. It’s what separates us from the animals … except the weasel" .... Homer Simpson

    Ah, Gotcha!

    Hmm... Only works when the text is too tiny to read. Whyja wanna shrink it so small? It just makes my eyes water!

    "Me transmitte sursum, caledoni"