Posts by AndyD

    I have multiple workbooks and each workbook can have anywhere from 1-20 worksheets and each worksheet is named differently. What I need to be able to do is combine all the worksheets into one master worksheet for each workbook (I don't want to combine workbooks). What I would like to do is create a template that I can use where all I would need to do is change the file name of the workbook and it would automatically combine the worksheets. Any guidance is greatly appreciated!

    Re: Linked Drop Downs Not Populating With Some Characters

    I have my sheet set up the same way as the MatchingListAdvanced.xls example in the Dependent Validation Lists. If I use that exact example and I change the column heading, "Expensive Cars" to "Expensive Cars & Trucks", the second list does not populate, but if I take out the "&" it works . The same thing happens if I put a hyphen in ("Cars-Trucks").

    I've created a spreadsheet that has two drop down lists. Based on what the user selects from the first list, the second list is populated. It's working great except for the items in the first list that use special character, such as a -, or &. If necessary I can upload a small example of how I have my sheet set up, but I think I found it on this site! :) Thanks!

    Re: Check Pivot Page Field Items

    Here is the final code that finds the correct product in the Pivot table, or goes to the next product if it does not exist.

    and here is the Function

    Function PIExists(PF As PivotField, ItemToFind As String) As Boolean
        Dim pi As PivotItem
        PIExists = False
        For Each pi In PF.PivotItems
            If pi.Name = ItemToFind Then
                PIExists = True
            End If
        Next pi
    End Function

    Thanks again for all your help!

    Re: Check Pivot Page Field Items

    Carl, your explanation makes sense. My thinking when I originally put this together was, since it's pulling data from a previous product, test to make sure the product was changed to the current product before importing the data. It makes a lot more sense to check if the product exists before trying to select it. And best of all… I finally got it working! Thank you for both of your help and suggestions!

    Re: Macro Pulling Data From A Pivot; Double Counting

    Here's the entire code I have so far

    I've also tried substituting you suggestion with a simple if statment:

    If ActiveSheet.PivotTables("PivotTable36").PivotFields("Product") _
            = arrProductVals(intProduct, 2) Then

    but that does not work either. Thanks again for your help!

    Re: Macro Pulling Data From A Pivot; Double Counting

    Hi Carl. Thanks for the help! I've been trying to get this to work and I'm still can't get it to pull correctly. It doesn't like my code!

    I have an array set up with values I want to look in a Pivot Field for and then pull data back based on that selection. It works great until there is an item in my array that is not listed in the Pivot Field. Then it pulls the data from the last item again, which skews my results. So my questions is, how can I skip to the next item in an array if it is not listed in the Pivot Field? Here is the section of code I have that I need help with:

    ActiveSheet.PivotTables("PivotTable36").PivotFields("Product").CurrentPage _
            = arrProductVals(intProduct, 2)

    I can post more of the code if necessary, but it's fairly long. Thanks for the help!

    Re: counting quantity of numbers, with conditions

    Is there an short way to count how many 1's in column A with a matching 1 or 2 in Column B? I know this works


    But I would like to get it to something like this:


    I would think the second way would run a little faster.

    I'm trying to determine how many months there are between todays date and a date in a cell. The equation works fine except if a date is not in the referenced cell, I will get #Value!. The equation I'm using is:


    Is there a way to say, if a cell contains a date, return the number of months, otherwise leave blank. I thought is would be something like the following, but could not get anything to work:

    =IF(B3 = DATE, DATEDIF(B3,Today(),"M"), "")


    Re: Creating Variables with a loop and then loop through them

    I ran into the same type of issue with my sheet and counted the number of variables to determine the size of the array

    Hope that helps!

    Re: Creating Variables with a loop and then loop through them

    I created the array and tried to fill it with the correct values. But when the portion of the code that runs to match that info up in another spreadsheet, it's not pulling the right data, so I'm assuming the array isn't filling correctly. I guess I'm fine having all the variables listed like they are. My main concern is getting this portion of the code down to a loop.

    This is only 3 of the 50 finds it goes through. Is there anyway to do a loop to step thought the variables since they are all sequential? Thanks again for your help

    I have code that creates 50 variables that reference a cell and then uses each of those variables to search for those values in another spreadsheet. I just named the variables accorting to the cell it's referring to in order to make it easier for me to determine it there was a problem pulling data. The code works, but is extremly long. I am looking for a way to shorten it up with loops to create the variables and loop through them, but I am having trouble figuring it out. Any ideas?

    this is just a portion of it, but you can get the idea of how long this monster is with 50 variables!