Posts by vanhunk

  • Display Excel AutoFilter Criteria when working with Tables

    In a previous Ozgrid Post the following method was given to display the AutoFilter Criteria with a function, this does however not work with Tables and the question is thus, how can the code be adapted so that it works with a Table (e.g. Table1)?


    Display/Show AutoFilter Criteria


    Excel's AutoFilter is one of Excel's most useful features. However, one small draw-back is it's hard top tell the criteria being used at a glance. The custom Excel function below can be used to display the criteria being used for each column of the table that has AutoFilter applied. All you do is ensure you have at least 2 rows above your table, then add the custom function to each cell 2 rows above the column heading. See below;



    [IMG2=JSON]{"data-align":"none","data-size":"full","src":"http:\/\/www.ozgrid.com\/images\/Pictures\/autofilt_criteria.jpg"}[/IMG2]


    The custom function is used in each cell shown above like;

    • =AutoFilter_Criteria(B3)
    • =AutoFilter_Criteria(C3)
    • =AutoFilter_Criteria(D3)

    Below is the code that must be added to the Workbook, or an Excel Add-in . To add the code to a Workbook go to Tools>Macro>Visual Basic Editor (Alt+F11) then to Insert>Module and paste in the code below;



    Thank you for your time.


    Regards,
    vanhunk

  • Return the last number in a range that is not in a coloured cell

    Re: Return the last number in a range that is not in a coloured cell


    Quote from Infomage;778157

    I am not aware of any way to do what you ask without using VBA.


    It occurs to me that this is precisely the type of scenario that makes VBA so useful.


    Formulae only work on cell values, not how that value is displayed, so I fear you're on a hiding to nothing.


    Infomage:
    Thank you for your reply, I will have to think how this can be done with VBA.


    Kind Regards,
    vanhunk

  • Return the last number in a range that is not in a coloured cell

    A Formula approach required that would add a 1 or any other number to the last cell above containing a number, but ignore numbers in coloured cells:


    For instance, something that would return the maximum number in the range above, but ignoring numbers in coloured cells.


    I would like to have formulas that would add 1 to the previous number in a column that is not in a cell with a coloured background. The numbers in the coloured cells are manual numbers and not results of formulas.
    [ATTACH=CONFIG]70196[/ATTACH]


    I have a function that returns the colour index of a cell that can be used.

    I used this function like:
    =IF(CellColorIndex(B6)>0,"",MAX($B$5:B5)+1)
    This works if there are no numbers in the coloured cells but formulas (which is not what is required) – see attached file.

    In other words, what is required is that the numbers in the coloured cells are ignored when determining the next number.

  • Hyperlink formula that uses path address in cell

    I am looking for a simple hyperlink formula that makes use of the path of the destination address as typed into a cell.


    Is this at all possible to do in Excel?


    I have the following in cell B5 on the worksheet '[Reference Manual Rev 4.xlsm]ACCOUNTABILITY'!$D$78 , I want a hyperlink formula uses the content of B5 to jump to the address/target it contains.
    Effectively I want a formula that does the same as the following formula would do =HYPERLINK("[Reference Manual Rev 4.xlsm]ACCOUNTABILITY!D78", A2)


    Thank you very much,
    vanhunk

  • Userform button to update edited data

    Re: Userform button to update edited data


    cytop


    I appreciate your concern, however, I really don't think starting a new thread would make any sense. The issue I raised is not a new problem and is 100% relevant to the existing thread. In fact it is the existing thread. If the purpose of the posts are for everyone to learn from it and not only to communicate directly with the originator, then it makes 100% sense that an issue with the solution posted should be posted in the same thread and not in a new one.


    Regards,
    vanhunk

  • Userform button to update edited data

    Re: Userform button to update edited data


    I am getting an error: " Run-time error '438': Object doesn't support this property or method. " when hitting save with a new entry or after editing an entry. It stops at the following line:

    Code
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear

  • Automatically Update date in cell value only if file is saved under a different name

    Re: Automatically Update date in cell value only if file is saved under a different n


    I have found a solution, although very weird.


    The problem I had is that doing it the way everyone suggested left the saved file not updated, unless the file is saved again, which I would not like to depend on. This could be fine on its own, but what I wanted to achieve is to have a date value in say, cell D3 which will update only when the file is saved under a new revision. I wanted to link it to the updating of the value of the formula. If I did as everyone suggested, this date value will only update when the file is opened up again and thus give the wrong date as date of revision, and still rely on saving the file again.


    I found a solution:
    What I have discovered is, that as long as there is a formula, any formula, somewhere in the workbook that has a result of "#REF!", formulas that use "cell("filename",A1)" anywhere in the formula will be updated before the file is saved under a new name, while saving. It is actually quite useful. It is also made what I tried to achieve possible. No other suggestion so far achieved the same thing.

  • Automatically Update date in cell value only if file is saved under a different name

    Good day,


    When I save the workbook as a new revision, I want to automatically change the date in a cell to the current date (i.e. the date the revision was changed). In other words I want this to happen automatically during the saving action.


    The file name is "PSIM Model Tool Rev..." where ... represents the version. I have a formula in the spreadsheet that extracts the ... part of the file name.
    The formula is:

    Code
    =SUBSTITUTE((MID(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),21,LEN(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1))-24))," ",".")


    The formula returns for example: "1.94", when the file is saved as "PSIM Model Tool Rev 1 94.xls".


    A date is given in another cell as, for example: "01-Oct-12", if I now save the file as "PSIM Model Tool Rev 1 95.xls", the date in this cell must automatically update to today's date, i.e. to "05-Oct-2012", but stays as "01-Oct-12" when saved only (i.e. when the name of the file stays the same).


    Thank you