Posts by andyjw

Re: no save warning on exit from document

Dave

I think this is something to do the goldmine so don't spend anymore time on this thanka for help

Andy

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

Using word 2003

Not getting a warning to save dcoument when exiting Is there a parameter to set to achieve this as other machines do get a warning in word 2003

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

Carl

Tested code works fine

thanks for all your help

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: Formula to make sure there is no #N/A

Try this

=if(isna(vlookup(B3,AUM!$A$5:$E$53,2,False)),0,vlookup(B3,AUM!$A$5:$E$53,2,False))

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;

=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;


Function AutoFilter_Criteria(Header As Range) As String
Dim strCri1 As String, strCri2 As String


    Application.Volatile
    
    With Header.Parent.AutoFilter
        With .Filters(Header.Column - .Range.Column + 1)
        
            If Not .On Then Exit Function
            
                strCri1 = .Criteria1
            If .Operator = xlAnd Then
                strCri2 = " AND " & .Criteria2
            ElseIf .Operator = xlOr Then
                strCri2 = " OR " & .Criteria2
            End If
            
        End With
    End With
        
    AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2
End Function
Display AutoFilter Criteria

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

Re: Show userform open file

Thanks for that works a treat

Andy

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

column P to R for lookup N8 shows month filter value selected and this is displayed as proper month in C5.
What i need is the proper salesman name in I5 when filter is selected for salesman.
with month filter and sales filter selected it produces monthly sales report by salesman in columns B to J

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