changing the data in an Excel formula automatically

[pendle666]

Hi

I've got a number of cells which give totals depending on the date. To update these all each month is time consuming and so I need a way to update them automatically.

Cell D12 is the start of the month - eg 01/09/2013 and F12 is the end - 30/09/2013

One formula is:

=COUNTIFS('Leavers'!$E$3:$E$1000,"UK",'Leavers'!$K$3:$K$1000,"<=01/09/2013")

But next month's report will need all the formula with 01/09/2013 changed to 01/10/2013 and formula with 30/09/2013 to 31/10/2013.

I tried to record a macro using the recorder, but this doesn't quite do what I need. I found a macro on Microsoft's website which will trigger an action when D12 or F12 are changed, but it's copying the content of that cell into my formula which is where I'm stuck. I thought I could put in the cell D12 or F12 in it's place but that doesn't seem to work with <= and >=

This is the Microsoft example:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
Set KeyCells = Range("D12:F12")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

' Display a message when one of the designated cells has been
' changed.
' Place your code here.

Range("B3").Select


B3 is the first cell where there is a date that needs to be change in the formula.


thanks in advance of your help

Pendle

 

[SkipVought]

hi,

this is NOT a VBA issue, but an Excel Speradsheet issue best addressed in forum68.

=COUNTIFS('Leavers'!$E$3:$E$1000,"UK",'Leavers'!$K$3:$K$1000,"<=01/09/2013")

But next month's report will need all the formula with 01/09/2013 changed to 01/10/2013

Is is a poor practice to hard code DATA in formulas. Rather, reference a cell that contains this data. Then merely change the data in the cell, or better yet, try to make the data in the cell AUTOMATICALLY calculae a correct value like...
[tt
=date(year(today()),month(today()),1)
[/tt]
to return the FIRST of the CURRENT YEAR/MONTH

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pendle666]

Oh righto, thanks. I thought because I was wanting a macro to do it that this was the place to be. But it looks like you've answered my question anyway, so thank you!

 

[SkipVought]

here's something else I'd also recommend: either using Structured Tables or Named Ranges.

Your formula might look something like this (based on the HEADINGS in yor table)
[tt]
this assumes a table named tLeavers on your sheet with headers of Country and SomeDate
=COUNTIFS(tLeavers[Country],"UK",tLeavers[SomeDate],"<="&MyDate)

this assumes a table with headers of Country and SomeDate
=COUNTIFS(Country,"UK",SomeDate,"<="&MyDate)
[/tt]
both of these techniques are much easier to understand and maintain.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pendle666]

Finally I have gotten round to getting this working.

And it works! Thank you very much for all your help - in future my formulae will be full of &startdate etc!


thank you for helping

____________
Pendle