thank you Carim, an easy work around
Posts by vbapadawan


actually it seems to return the already hidden rows that meet the criteria too? down the left you will see S and A in white font, I have code that hides rows with the S.
It does the same thing if i autofilter the names and then remove the filter

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?

Thanks Carim, I did think database when i first stared but decided against it... we all nee a challenge
I have updated the sppreadsheet with the answer. basically its a filter between two dates and looks at P or Isblank, then returns all the corresponding names, etc.

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.

Re: find today() within a range of dates and highlight cells
Thank you Dangelor, that's perfect.

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. 
Re: Sum the values in one column only unique vauels from another
OMG Thank you so much
[Blocked Image: http://www.ozgrid.com/forum/image.php?u=230998&dateline=1386501164] XOR LX
, it worked perfectly. I would have never thought of that. I've never seen the n( what does that do?

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.

Re: Help with refining Advance Filter code to add aditional critera trigger
I'll create an example workbook as the information is sensitive

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"))Code
Display MoreOption Explicit Sub RunFilter(newVal As String) Dim rngData As Range Dim rngCrit As Range Dim rngTrigger As Range 'Where is the criteria? Set rngCrit = Worksheets("Formulas").Range("CritRange") 'Where is data? Set rngData = Worksheets("OB").UsedRange 'Cell we use to control criteria Set rngTrigger = Worksheets("Formulas").Range("H1") On Error GoTo cleanUp with application .ScreenUpdating = False .EnableEvents = False .Calculation = xlCalculationManual end with rngTrigger.Value = newVal 'Calculate new criteria rngCrit.Calculate rngData.AdvancedFilter xlFilterInPlace, rngCrit Application.Goto rngData.Cells(1), Scroll:=True cleanUp: with application .Calculation = xlCalculationAutomatic .EnableEvents = True .ScreenUpdating = True end with End Sub

Re: Excel 2003 Double click result to advance filter complex criteria in sheet data
thanks guys
thats awesome I've never really used advanced filters and now I will

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;766312Hi 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 FuzzHead, 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?

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

Re: How do I simplify a COUNT(IF startement
I forgot to mention that on that I am using excel 2003 and cannot use countifs
Quote from MrRedli;765975why don't you use countifs