copying data from a cell into a formula which exists in another cell

[pendle666]

Hi

I thought this might have been a VBA issue, but I've been told no, so apologies to those who are reading this again.

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 (it forms part of the report heading)

One of my 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 was advised to use

=date(year(today()),month(today()),1)

but then I realised that this isn't going to work for me as I may not work on my report until a week into the new month and I may need to use old dates which is why the date range is entered in D12 and F12 and I'm using <= and >=.

The report is basically giving a list of leavers in UK who's start date was on or after 01/09/2013.

The formula has been altered for confidentiality, so excuse me if I have the < and > the wrong way round.



thank you for helping

____________
Pendle

 

[SkipVought]

hi,

I thought I have posted something that would work for you as "reference a cell that contains this data. Then merely change the data in the cell"

so you formula might look like...
[tt]
=COUNTIFS('Leavers'!$E$3:$E$1000,"UK",'Leavers'!$K$3:$K$1000,">="&MyDate)
[/tt]
Where MyDate is a cell Named Range containing the date you want to use.

Skip,

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

 

[SkipVought]

Cell D12 is the start of the month - eg 01/09/2013 and F12 is the end - 30/09/2013 (it forms part of the report heading)

I didn't read your post closely enough. There! You already have the date in D12. So reference $D$12 in all your formulas.

Now if you want to have some fun, and I really think that using Excel make lots of opportunity for fun, you might consider a spinner control, whereby you could add or subtract month(s) from the current month start date. Suppose, for instance, that the spinner control put a value between -3 and 3 into A1, then with a click or 2 you could manipulate your start date via a formula like this...
[tt]
=date(year(today()),month(today())[highlight]+A1[/highlight],1)
[/tt]
Sky's the limit!

Skip,

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