Posts by Relman

I think the answer might be in this thread:

http://www.ozgrid.com/forum/viewthread.php?tid=5773

Good Luck!

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 your help Andy and Ritchie

It was my bad all along I had the VBE Design Mode button selected - DOH!

Deselected it and now it works fine, of course.

Thanks again for your time and perseverence in the face of utter stupid-idlyness!

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:

=B7*EXP(GAMMALN(1+(1/B8)))

And this formula in C9:

=B4-B9

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")
Else

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.

Here's Derk's as well:

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?

Damn! must refresh my browser before posting!
Thanks Andy, I'll look and let you know Pronto!

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.

Brilliant Ritchie! :congrats:

The solution was right in front of my eyes;

Set Fillstart = Range([First], [First].End(xlToRight)) :duh:

Thanx M8!

Hi Pesky, thanks for replying :wink2:

Alas, I've tried that, and it bombs out on that line with "AutoFill Method of Range class failed"

Same thing if I use both Rowi and Coli on the Offset().

Any other thoughts?

Chris

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

Chris

Varium et mutabile semper Excel

Try the attached.

The items for the ListBox are on the sheet "data". Click the button on "data" to show the form, then select an item from the list. Click OK to dismiss the form, then have a look at "Projection" - the value you chose is in E1 :yes:

Hope this helps

Chris

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

HTH

Chris

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,

Chris

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

Chris

What wonderful labels??

Chris

"Varium et mutabile semper Excel"