Posts by andyjw

    Re: no save warning on exit from document


    Dave


    Its the X button that closes the word window that is being applied by a user and the warning Do you want to save changes YES NO CANCEL that is not being display to propt the user to save document


    It must be a check box in TOOLS OPTIONS maybe##


    Andy

    Hi all


    I have a workbook with 100 sheets each contians the same form
    When a sheet contians AB or NB in cell J20 I want to email the active sheet to a receipiant when saved


    Any help would be greatly received


    Andy

    Re: Reset to zero


    CarlMack


    Yes that was successful


    Any chance on some code to clear Discount Set only to zero
    range is G4 and F7:F26


    Andy

    Re: Reset to zero


    CarlMack


    If put a figure in Discount Set h10 say the code works through all the sheets


    H column is blank in Discount Set


    Any way to work around thid

    Re: Reset to zero


    Carlmack


    Code only changes data in h4:h40 on Discount Set and do not look at other sheets


    I do not want any data on Discount Set to be changed it should just look at other sheets if poss


    Andy

    Hi Guys & Girls


    I have a workbook with 20 worksheets the first is a summary called Discount Set the 19 sheets with various names


    The 19 sheets in column H4:H40 u enter a number greater than 0 which is the quanty required. This is used to calculate a price on which ever row it is entered


    the selection must only affect numbers as there are rows that are just shading and they contain no data .


    So u could enter quanties on serval rows of different sheets to get a total price displayed in the Discount set


    What I want to do is have button on the Discount Set which will clear any quantity that does not equal 0 back to 0 in column H which will reset the total 0.00


    Any help


    Andy

    Re: Show autofilter criteria


    Roy


    Thanks for tidying up code could u help adding to it to give the results to my first query i.e changing the header to a colour when the filter is selected etc
    Tried the code u suggested did not give what i needed


    Andy

    Re: Show autofilter criteria


    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;




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

    Dave


    You posted this code and it works well


    could u advise how to display just the criteria ie no heading, no :, no =, and when it displays the criteria could it fill the cell with a colour. When filter is set to "all" give a blank cell


    This would over come lots complaints from operators not realizing that filters are on because they can not find the silly blue button.


    Are microsoft aware of is and are they changing it in the new release.


    Andy Hopr u can help

    Hi All


    I need to display a userform which includes a label & command button when the excel file is opened. User reads message click command button to clear userform and then continue in excel


    Any help urgent


    Andy

    Re: Vlookup


    this formula will work for you


    =IF(ISNA(VLOOKUP(A:A,Sheet2!A:C,3,FALSE)),"Not in list",VLOOKUP(A:A,Sheet2!A:C,3,FALSE))

    Re: Row numbering macros to renumber rows


    If all the cells in a column starting from row 6 have data entered you can use this formula at the bottom of the column


    =subtotal(3,range:range)


    If you use excel 2003 use


    =subtotal(103,range:range) allows hidden rows to be excluded from total


    Remember to insert rows to move formula down when you need new rows


    Say that column A is filled with data from a6 to a500 use formula


    =subtotal(3,a6:a500)


    This will keep spreadsheet same size


    Andy

    Re: Show filter value in a cell


    Hi All


    See file attached Sales Results


    I can achieve the result of putting the salesman name in cell J2 by assigning salesman initials a number as in column B and an average formula in B68 via the lookup tables, the same method is used to insert the month in D2 when a filter is selected on columns L and M.
    What i need really is the filtered value of column L (Salesman initials) placed in L68 when the filter is selected.


    Hope this explains the problem


    Andy

    Re: Show filter value in a cell


    Trigeering is a problem as u say, when the filter is set would be ideal.
    Iam trying to a static value of filter choosen to use in a vlookup of saleman initials which i filter on to proper names. I did it on the month which is either 1 2 3 etc in acolumn by placing a subtotal that averages so when i filter on say 3 the answer is always 3 no matter how many enteries in the column Then use result in vlookup to return proper month March.
    Any other way Vlookup wont work on filter range


    Andy