Posts by Brandtrock

    Re: Dave Hawley Hits 20000 Posts

    But Will, your pictures make you look much taller than that!

    I think I've read some of his posts after he comes back from the boozer.

    Just kidding.


    This is a great resource for everyone who uses, works with, or plays around with Excel. All user levels can find content suitable to their level of knowledge and abilities. Dave's vision for OzGrid and the many ways it assists the Excel community was grand and he exceeded his own expectations. Of course, a key component in being able to build this place was to get enough quality helpers (dwarves, elves, halflings, whatever...) to make the forum the best place to get an answer on Excel.

    If Dave wasn't the kind of person he is, there is little chance that he could get the quality individuals that he has assisting him to stick around and do the work they do.

    Congratulations on the quantity but more so on the quality of the posts you have provided Dave. Keep up the good work!!


    Re: Set Multiple Pivot Tables To 1 Source Range

    For Each ws In Worksheets

    Is the line that goes through each worksheet in the Worksheets Collection, regardless of name.

    For Each pt In ws.PivotTables

    takes each pivot table in the PivotTable Collection in the currently active worksheet.


    Re: Vba: Type Mismatch

    Quote from turtle44

    ... not knowing what type of info a variable will hold is usually (though not always) a result of poor programming.

    Agreed, and shg's points are well taken also. Given the OP's assumed experience, I wasn't sure if the default to Variant type was known to the OP and I knew if I only put that hint in that someone would follow up with the explicit declaration.

    Knowing what type of data is going into a variable is good programming practice that I not only follow, but recommend. The use of variant type variables for the uses described is also, IMHO, good programming.

    Thanks for the additional comments as they make the thread more useful to anyone searching the forum in the future.


    Re: Vba: Type Mismatch

    FYI - when in doubt about what a type of info a variable will hold, or if it might hold more than one type, you can simply use a variant type.

    This can be explicitly declared like

    Dim MyVariable as Variant

    or leave it like this

    Dim MyVariable

    and VBA assigns type Variant by default.


    Re: Vba: Type Mismatch

    A type mismatch error occurs when a variable is assigned a value that is not the type declared for that variable. For instance, assigning a string (like Yes or No) to an Integer type variable.

    Without seeing your code, it is next to impossible to tell you exactly why this is occurring.


    This shows a nifty way to implement a Pivot Table to reconcile many accounts. The basic concept is fairly simple to implement so long as you can get your two data sources in electronic format.

    The file is too big to upload here. It can be downloaded from here along with other freebies. Or here in zip format PivotTable Reconciliation

    Hope this is useful to a few persons.


    Re: Congratulations On 10,000 Posts, Andy Pope!!

    I've been on vacation for a couple weeks, glad I got back in time to see this. I've learned so much about charts from your posts here and from your site. Thanks for taking the time to share your knowledge with the rest of us.


    Re: Fifo Inventory Valuation


    I like your template, good work.

    In the instructions (both in the workbook and on the Excel-it site), you refer to a sheet named Stock. The example has three sheets named (Explanation, FIFO, and Lists). The FIFO sheet does what is described as being on the sheet named Stock. Just thought you'd like to know.


    Golfing with Your Wife

    A man staggered into a hospital with a concussion, multiple bruises, two black eyes, and a five-iron wrapped tightly around his throat.

    Naturally, the Doctor asked him, 'What happened to you?'

    'Well, I was having a quiet round of golf with my wife, when at a difficult hole, we both sliced our balls into a cow pasture. We went to look for them and while I was looking around, I noticed one of the cows had something white in its rear end.'

    I walked over, lifted its tail, and sure enough, there was a golf ball with my wife's monogram on it - stuck right in the middle of the cow's butt.'

    'Still holding the cow's tail up, I yelled to my wife, 'Hey, this looks like yours!'

    I don't remember much after that...

    Have a great Friday everyone,

    Re: Norminv Function/Formula

    The p, in all likelihood, is going to be a cell reference, so:


    will give you the z score for the p value in cell A1.

    Adjust the cell address as appropriate for your spreadsheet layout.