Posts by widgetwonka

    Re: Run time error '13': Type Mismatch

    Not sure what is wrong with that code. It would be faster to use the filter/hidden cells method though. It is well covered on the internet.

    You might have better luch declaring garbage3 as an integer.

    Re: Excel dynamic list query


    In sheet1, I put Language in column A and Book title in column B.

    For example:

    A B
    1 French French1
    2 Spanish Spanish1
    3 French French2

    I then made a tab named French. This sheet has one column, A, named Book.
    The result should be:

    1 Title
    2 French1
    3 French2

    There are two ways to approach the reference to "French." The first is to type it in a cell and reference it. The second is to use a formula to find the name of the worksheet. Which you choose is your preference.

    Reference method:
    I typed French into cell E1. In cells A2:A15, I used the following formula:

    The brackets around the formula indicate that it is an array formula. To enter one of these, enter everything but the brackets. Then, when you are done entering the formula, hold down control + shift and then press enter. That will tell Excel that you are using an array.

    The formula breaks down as follows:

    IFERROR - tells Excel to suppress errors (since you will want to copy this formula down, this is an easy way to do it).
    INDEX - finds the cell in the array
    SMALL - dictates which position in the array to return
    IF... - creates the array of French books by comparing the language in sheet1 for each line to the reference language. If they match, it records the row of that record in the array.
    ROW() -1 - Tells the small formula what position to use, based on the row in the French tab that the formula is on. Since I referenced the array from A1, I need to subtract one to compensate for the data starting in A2

    Formula Method:

    The breakdown is the same, except that
    is used to find the sheet name. There is some parsing going on in it to find just the sheet, since otherwise, it will bring back the entire path.

    To use this formula:
    I ran my formulas down 15 lines. Just change 15 to whatever number you want. Be sure to do it for all instances of 15. Then, copy the array and paste it down as many times as you like. A special note: you can't copy and then paste an array over itself. Excel will tell you that you are trying to change part of an array. So, just copy A1, then select A2:Awhatever you want, and paste the array. When pasting arrays, you do not need to use the control + shift + enter method.

    I hope this gets you what you need.

    Firstly, I should state that my knowledge of the Microsoft Internet Controls and MSHTML libraries is low. I started using them recently to try to automate the downloading of excel reports from a webform. As such, this has been a learning experience, and my code definitely reflects that.

    The task:
    I have a webform which is all scripted in Java. After initializing the page, a javascript determines what tab to display, etc. The result is that the page requires a generous amount of navigation to get to the input elements and to the export link (My guess is there is a better way for this as well, but that is not in scope for this post). I want to fill in the form, then click the link to export the results to excel.

    The problem:
    I have been able to fill in the form and get it to fire the export link. I am stuck here.

    After I export the report, IE (I am restricted to IE at work...) opens a new window and then an Excel dialogue box opens with options to open, save, or cancel the document sent from the server. The file name of the export is not constant, and I don't know how to get the path of the export to feed to Excel.

    I know methods to access the workbook after I open it, but I am stuck on how to actually open the workbook. Any help would be appreciated.

    Obviously, posting the URL would be the best way, but it is restricted, so I cannot.

    The code:

    Re: VLookup across Multiple Sheets

    Thanks for cleaning it up Sierra. I didn't test it, so I am not surprised it needed some revision. You don't see a lot of first time posters with that level of debugging acumen. A big welcome from one Excel junky to another!


    If you go the PT route, GETPIVOTDATA is also a good option, since you can configure the lookup across multiple dimensions.

    Re: how to remove entire rows if any cell contains numbers over 1500

    In its most direct form, you can use a loop statement to do this:

    To use it for any workbook, but in in your personal workbook in the VBE. I set the loop to end at cell 150000, but you can change that to whatever number you want.

    Re: Aging Inventory

    I put your values in a spreadsheet, starting in A1. Here is the formula for cell D1:


    This is an array function. To enter it, enter everything but the brackets. Then, hold ctrl+shift while pressing enter to exit the cell. The formula creates an array, and then find the smallest value. I set the alternative value to 10000, which should be OK given your buckets.

    Re: Aging Inventory

    So, if the item was used in the last 30 days, the bucket is 0-30days
    and if the item was uned in the last 60 days, the bucket is 0-30 days?

    I am not sure I understand your question.

    what about =IF(TODAY()-"Last used date" <= 30, "0-30Days",IF(TODAY()-"Last used date" <= 60,"31-60Days",">60Days")))