Posts by AndyD

  • Combine Multiple Worksheets In One Workbook

    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!

  • Linked Drop Downs Not Populating With Some Characters

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

  • Linked Drop Downs Not Populating With Some Characters

    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!

  • Check Pivot Page Field Items

    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


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

  • Check Pivot Page Field Items

    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!

  • Check Pivot Page Field Items

    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:


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


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

  • Check Pivot Page Field Items

    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!

  • Check Pivot Page Field Items

    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:


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

  • counting quantity of numbers, with conditions

    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


    [COLOR="Blue"]=SUMPRODUCT(($A$2:$B$277=1)*(BA$2:$B$277=1))+SUMPRODUCT(($A$2:$B$277=1)*(BA$2:$B$277=2))[/COLOR]


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


    [COLOR="Blue"]=SUMPRODUCT(($A$2:$B$277=1)*(BA$2:$B$277=AND(1,2)))[/COLOR]


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

  • Determine if a Cells value is a Date

    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:


    =DATEDIF(B3,TODAY(),"m")


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


    Thanks!

  • Creating Variables with a loop and then loop through them

    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!

  • Creating Variables with a loop and then loop through them

    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

  • Creating Variables with a loop and then loop through them

    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!