Posts by mangier

    I am trying to write code to open a new (blank) workbook while in an existing workbook (I am then going to pass data between the two which is easy). When I try to record the code to get the syntax for opening a new workbook it will not record any code. I also need to name the new workbook based on text in a cell in the existing workbook. I just saw before posting this that the code;


    will open a new workbook now I just need to name the new workbook based on text in my existing workbook.

    Re: Refer To Dynamic Range Indirectly

    Thanks for looking at this. I must have mistyped something because this totally works. Sorry for posting this question. Moderaters please feel free to delete this thread.

    Is there an equivalent to refering to a range of cells indirectly the way you can refer to a single cell indirectly using the INDIRECT() formula or something like it? If I type "B1" into cell A1 and type "=INDIRECT(A1)" into cell C1 then the result will be what ever is in cell B1.

    How do I pass a range of cells into a formula indirectly (i.e. the contents in cell A1 is "A2:A100" and I want to pass this into another formula). This doesn't work but maybe it will illustrate what I am trying to do;


    where A1 contains the text string A2:A100

    Re: Extract Top X Maximum Values

    Thanks again for your recommendations. I like the use of the MIN feature in your pivot table and it makes sense to uncheck the items with zero balances but this would be tedious if you had a few hundred items to scan through and uncheck all the zero balances. I was still hoping someone might have a more automated approach to solving this with formulas. I can always write a macro to do this (I am familar with manipulating and automating pivot table w/ VBA code) but it is nice and usually educational to find alternative solutions that can be gained from just using formulas.

    Re: Extract Top X Maximum Values

    This is a great solution for extracting the non zero smallest values but I don't know how to extract the adjacent information to the smallest values. In otherwords, if you have a table of names and new salaries (as I do) and you want to extract the lowest new salaries and their names then you need a vlookup or matching and indexing formula to get the names with the lowest values. I also have zero value salaries within this data from terminated employees whose new salaries are now zero so I need to exclude those names and values when extracting the data.

    This array formula works great for extracting the names associated with the five highest values;

    =INDEX($A$2:$A$8, MATCH(LARGE($B$2:$B$8 - ROW($B$2:$B$8) / 10^5, ROW() - ROW(A$11) + 1), $B$2:$B$8 - ROW($B$2:$B$8) / 10^5, 0) )

    where column A has the names and column B has the salaries and Cell A11 is where you want to start this formula. Now how do I combine your straight forward formula;


    with the above formula to find the names associated with the lowest new salaries in column A? A vanilla vlookup formula will have problems indentifying several employees with the same salary (it will likely show the 1st occurance of the name and not subsequent names with the same salaries) but there may be a more crafty way to structure a vlookup with a dynamic range argument to pass by the 1st occurance after it has found it and move on to the next one.

    Re: Extract Top X Maximum Values

    Thanks again for your help Dave. Pivot Tables and custom views are all good options but I am trying to stick to the array formulas because of the versatility.

    Can anyone help with a formula for extracting the 5 smallest values that are non zero?

    =INDEX($D$2:$D$8, MATCH(SMALL($E$2:$E$8 - ROW($E$2:$E$8) / 10^5, ROW() - ROW(D$11) + 1), $E$2:$E$8 - ROW($E$2:$E$8) / 10^5, 0) )

    Unfortunately, this one above will extract zero values from a data range as the smallest 5 values.

    Re: Extract Top X Maximum Values


    This is pretty neat and I am learning more about using the advanced filter but I am still having trouble so I have attached the sample again to show you what my results of the Advanced Filter were. I have included the exact parameters I am using in the Advanced Filter dialog box. For some reason my results for the bottom 5 show the zero value and not the "1" value and they do not appear in ascending order. And the top 5 does not appear in descending order.

    Also this approach, although very useful, is not as automated as the array formula approach which has the advantage of updating the results dynamically without the user having to rerun the advanced filter if the the data is modified.


    Re: Extract Top X Maximum Values


    The other formulas above work fine but I wanted to try your advanced filter approach since I thought that was going to be the best solution when I first tried to solve this problem but I keep getting "reference is not valid" which means I am obviously using this approach wrong.

    I am also trying to use this knowlege to extract the 5 smallest non zero values from my data range as well. Any further suggestions would be greatly appreciated. I can attach another sample workbook if that would be easier to show you where I am having problems with your advanced filter solution.


    Re: Extract Top Five Maximum Values From A Range

    I hate when the solution is so eloquent that I would have had no chance to solve it on my own. Its like getting check mated in the first 10 moves. Thanks VBA Noob.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]VBA Noob,

    I found a minor glitch that I can't sort out. If I insert a row above my data range and modify your formula in the following way, it will not show the two largest values;


    I have attached a sample workbook to show how your original formula works and how my modified formula fails.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]OK I found my error. The formula needs to be altered in the following way;


    I have changed the row reference from Row(A2) to Row(A1) in the Match formula. Hope this helps anyone else who looks at this thread.

    How can I extract a list of the top five maximum values from a range of cells. This is similar to using the the "top ten list" in the Auto Filter but I don't want to filter my data in place. Instead I want to extract a list sorted in descending order along with desciption data located in adjacent columns for each maximum value.

    In other words take the following list with descriptions in column A and values in column B

    Item 1 = 10
    Item 2 = 10
    Item 3 = 2
    Item 4 = 20
    Item 5 = 4
    Item 6 = 100
    Item 7 = 5

    and extract the top five values and their desciptions to another location in descending order

    Item 6 = 100
    Item 4 = 20
    Item 1 = 10
    Item 2 = 10
    Item 7 = 5

    I am wondering whether this can be solved with a combination of matching functions and indexing functions or whether I will need VBA code to solve this.

    How can I change the text color within a cell when I am using a concatenation formula? I have the following formula;

    ="Total Bonus Earned ("&TEXT(A2/VLOOKUP($A$1,PR_DB,2,FALSE),"#0.0%")&" of 2006 Earnings):"

    In the text function portion I would like to format the result in red. An example of the result of my formula I am seeking is as follows;

    Total Bonus Earned [COLOR="Red"](10% of 2006 Earnings)[/COLOR]:

    Do I need to create a custom format and if so how do I call it up within the text function?

    Re: Import Access Query

    Ok, now that I am in the right forum (Thanks Moderators) I did some searching and found this link about importing from Access with VBA.

    I am struggling a bit because I don't understand the "tip" fully.

    In Step 1, I don't understand what reference I need to add to the ActiveX Data Object Library

    In Step 2, do I enter this code into a standard module in Excel via the VBEditor or do I put this code into Access (I am very inexperienced with Access obviously)

    And finally, what is MDAC? Is that ActiveX?

    I am trying to automate the importing of several Access queries into Excel. I am somewhat proficient at writing VBA code for applications within Excel but have never tried to write code to import data to Excel.

    Access does a nice job of exporting its queries to Excel (Tools|Office Links|Analyze it with Microsoft Office Excel) but I don't know how to change or direct its default path. It seems to create an Excel file with a "dbo." prefix followed by the query name and put it into the "My Documents" folder. I would be very happy just to learn how to change that default setting to direct the query results to a specific page with in a desired Excel workbook.

    Ultimately, I would like to learn how to run an Access query and export it to Excel entirely through VBA code which is initiated within Excel. I would appreciate any suggestions on how to go about learning this process.

    I am trying to find a way to count the total number of pivot fields in a pivot table so I can remove ghost pivot items that are no longer in the pivot table data. My code for this subroutine is as follows;

    My code makes an assumption that I have 10 Pivot Fields or less. It would be nice to actually know the number of Pivot Fields so my "For Count" Loop would be more efficient.
    In otherwords;

    For Count = 1 To Total_Number_of_PivotFields
            On Error Resume Next
            For Each ghost In pt.PivotFields(Count).PivotItems
            Next ghost
        Next Count

    Also, I don't know whether I need to turn on the ManualUpdate to speed up the code but I thought I would try it.

    Re: conditional format to identify formula cells


    I don't understand your solution. I have never written a UDF so that is part of my problem. Is "HasFomula" the name of this UDF?

    I tried Bob's code

    Function Is(rng As Range) As Boolean 
        If rng.Count > 1 Then 
            IsFormula = CVErr(xlErrValue) 
            IsFormula = rng.HasFormula 
        End If 
    End Function

    but I wasn't successful in setting up the UDF. Is his UDF named IsFormula with rng as its argument?

    Re: Get Pivot Table Name

    Thank you Will. I have written some generic VBA code that allows one to show all pivot items for a given pivot field in a given pivot table. With your last addition in line 5 below I think this code seems to be pretty bullet proof now. I am fairly new to writing VBA code so if you have a chance to review this subroutine I would appreciate your feedback.

    How do you get the name of a pivot table in VBA? I recorded a macro to see what happens when you change the Pivot Table name in the Pivot Table Wizard and I got the following;

    ActiveSheet.PivotTableWizard TableName:="PivotTable2"

    So I thought I would try this code;

    PivotTableName = ActiveSheet.PivotTableWizard.TableName

    but it doesn't work. Please help so I can positively select a pivot table in my VBA code without having to always check its name manually and then enter it into the code.