Posts by vbapadawan

    OMG Carim, you are a genius, why didn't I think about an autofilter. It seems to work well. Thank you so much.

    I'll keep the cells and simply change the font colour to white.... easy pezy

    Just to be cheeky, how would I bring back this information minus hidden rows?

    Hi to all

    What I am trying to do is search the leave calendar (sheet APS), find those not on leave (blank or P (P is in white so not visible)) from the APS sheet, between the two dates specified on the Duty Picker sheet, and list them in on the duty Picker sheet.

    I am not sure where to start.

    Any assistance would be muchly appreciated.

    I have attached the example workbook that I am working on.


    Example Leave Planner.xlsm

    Please help


    I have a spreadsheet that has set time periods by groups of date ranges.
    As you can see in cell E1 i have used a formula, but that will only work on the adjusted table below the one I want to use.
    What I want is some VBA so when the workbook is opened that the current period (within A4:A15) to be populated as E1, the corresponding CY added to F1 and the corresponding date range (start/Finish cells) highlighted (interiorcolour).
    I have attached the workbook.

    I am using Excel 2003 and am having a hard time doing a seemingly simple task... I have seen many formulas in 2007 up that are easy...why not 2003


    All I want to do is to sum the values in one column but only the first/unique instance of items in another column (ignoring blanks)


    As you can see below, there are occasionally duplicate cheque numbers and blanks, but I want to only sum the unique ones at the bottom in a "Totals" cell.

    Hi all


    I have the following code which works brilliantly for one criteria, but I want to add another, can you please help?
    I want to add another control trigger (Set rngTrigger = Worksheets("Formulas").Range("H1"))



    Re: Excel 2003 Double click result to advance filter complex criteria in sheet data


    Hi Luke M


    I can't see where the formula knows what column in sheet("SOH") to filter the data on?
    I understand how the formula sheet is set up, but i have gone through the VBA and can't seem to find how to target columns? The example I gave was for one sheet and one line, my actual workbook has multiple lines in the dashboard that prtain to different sheets, some of them are formatted differently than other (the Depot code and Days columns are not always A & G)
    Could you please shed some light for a Noob... :) ?



    Quote from Luke M;766312

    Hi vbapadawan,


    It can be done nicely using AdvFilter. Check out the attached file. Macro is triggered when you double click on cell in B3:E3 of dashboard sheet. I setup the criteria for filter on the new Formulas sheet.

    Re: Excel 2003 Double click result to advance filter complex criteria in sheet data


    thank you Luke M, you are a legend. wow


    thank you Fuzz-Head, even though it wasn't what i was looking for, it has given me ideas on how i can target depots.


    thank you StephenR for thinking of me :)

    I have a tricky one for the gurus.


    I have a workbook that has a dashboard which is simply a summary of "Days after dispatch", but there is a complicated method to work these figures out.
    On the attached example workbook there is a legend where there is multiple criteria to take into account.


    Depot group A are calculated differently than Depot group B on the Green, Amber and Red columns but fall under the same headings.


    I need a way that when you double click on the number result below the Current,Green,Amber and Red that the corresponding data in worksheet SOH is filtered accordingly to show the data result.


    Now I know that excel 2003 won't allow multiple criteria (array) in an autofilter and I am not sure how to code an advance filter to cope with multiple complex criteria.


    example (Amber)
    I need the result to contain the records from Depot group A result (array of depots and date parameters) + the records from group B result filtered in situ or in another worksheet.


    Can this be done?

    Re: How do I simplify a COUNT(IF startement


    Couldn't get the range thing to work, but may have cracked it?

    Code
    =SUMPRODUCT(--(('532 CAT DR'!C$2:C$65536="JLSQ")+('532 CAT DR'!C$2:C$65536="RSER")+('532 CAT DR'!C$2:C$65536="AABN")+('532 CAT DR'!C$2:C$65536="AMBW")+('532 CAT DR'!C$2:C$65536="OAKW")+('532 CAT DR'!C$2:C$65536="AAWL"))*('532 CAT DR'!I$2:I$65536>=30)*('532 CAT DR'!I$2:I$65536<=60))+SUMPRODUCT(--(('532 CAT DR'!C$2:C$65536="JRPS")+('532 CAT DR'!C$2:C$65536="RDSP")+('532 CAT DR'!C$2:C$65536="DISP")+('532 CAT DR'!C$2:C$65536="JCLT")+('532 CAT DR'!C$2:C$65536="OPS1")+('532 CAT DR'!C$2:C$65536="SERC")+('532 CAT DR'!C$2:C$65536="JMEA")+('532 CAT DR'!C$2:C$65536="ACEA")+('532 CAT DR'!C$2:C$65536="CLTH"))*('532 CAT DR'!I$2:I$65536>=30)*('532 CAT DR'!I$2:I$65536<=90))

    Re: How do I simplify a COUNT(IF startement


    can I use Sumproduct using a range? if I put JLSQ,RSER,AABN,OAKW in a range and all the others in another range could i simply call the range?


    Code
    =SUMPRODUCT(--('532 CAT DR'!C$2:C$65536=W)*('532 CAT DR'!I$2:I$65536>=30)*('532 CAT DR'!I$2:I$65536<=60))+SUMPRODUCT(--('532 CAT DR'!C$2:C$65536=Retail)*('532 CAT DR'!I$2:I$65536>=30)*('532 CAT DR'!I$2:I$65536<=60))

    Re: How do I simplify a COUNT(IF startement


    thank you mikerickson


    what if I wanted to add critera? for instance, I want to count all records in "JLSQ", "RSER","AABN" & "OAKW" that have a value between 30 and 60 then I want to add a count of all records <> "JLSQ", "RSER","AABN" & "OAKW" that have a value between 30 and 90

    Re: How do I simplify a COUNT(IF startement


    my head hurts, what am I doing wrong. Code 1 result = 14 which is correct, but written as Code 2 result = 16? it seems that the formula is counting all records bewteen 30 and 60 and not just the ones in "JLSQ"


    code 1

    Code
    {=COUNT(IF('532 CAT DR'!C$2:C$65536="JLSQ",IF('532 CAT DR'!I$2:I$65536>=30,IF('532 CAT DR'!I$2:I$65536<=60,0))))}


    Code 2

    Code
    {=COUNT(IF(OR('532 CAT DR'!C$2:C$65536="JLSQ"),IF('532 CAT DR'!I$2:I$65536>=30,IF('532 CAT DR'!I$2:I$65536<=60,0))))}