Automatic Functions 2

[Angel79]

Is it possible in Excel to have a function to automatically start and end on a certain date?

I have a function set in a cell (B1) to read a cell (A1) in a different worksheet but would like it to automatically start come January and end at the end of the month and then another cell (B2) read from that same cell (A1) at the beginning of the next month (February) etc.

Is this even possible??

Thanks,
Christa

 

[koresnordic]

yes. Next question



Graham

 

[koresnordic]

sorry . Do you want the final result for jan to always be displayed in b1 or for it to be blank? What is to be shown in b2 prior to feb?



Graham

 

[Angel79]

LOL..funny people here!

B1 always shows Jan and once Feb turns around the final Jan stays in B1 then Feb displays in B2 etc..

if its not possible to have Jan to stay than display 0

In B2 prior to Feb display 0

Christa

 

[koresnordic]

It should be possible to have the results stay, but I am on limited time at the moment, so firstly how to get the result.
Place the following in cell b1 "=IF(MONTH(NOW())=1,A1,0)"
This will check the current montha nd if it is jan, place the value in a1 in b1. Do the same for b2 to b12 - except replace the month to be checked to 2,3 etc. This will show a 0 if not current month or the value in cell a1.

Maybe someone on more time will place how to make this fixed at end of month.



Graham

 

[Angel79]

Ok thanks Graham for your help.

Also, what if the value has to change halfway through the month..or an exact date ie. Jan 15, Feb 20, etc. Any possible ways then?

Christa

 

[Bowers74]

Another question:

Where is the cell A1 in the other sheet receiving the data? Is it another formula that sums the totals from a table or somthing?

You might get a more specific (detailed) answer if you posted your problem better. ;-)



Happy Ho Ho!!! [Cheers]

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[Angel79]

The answer I received from Graham (koresnordic) worked perfectly but now I'm wondering if I can have the formula work for a specific date rather than just the month.

And to answer your question Mike, cell A1 is receiving the data from other cells (using MAX(F4:F49) from another worksheet.

So, now the only question is, Can the formula "=IF(MONTH(NOW())=1,A1,0)" be specific to day rather than just month (ie. Jan 15 rather than just Jan)

Thanks,
Christa

 

[KenWright]

=IF(TODAY()=DATEVALUE("22-Dec-2003&quot,A1,0)

Regards
Ken................

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[Angel79]

That will make it only appear on that day though correct?
So, if I want it to appear if its any day before December 28 I use....?

And if I want it to be any Jan between November 28 and Dec 28...?

Thanks again,
Christa

 

[KenWright]

=IF(TODAY()<=DATEVALUE(&quot;28-Dec-2003&quot,A1,0)

and

=IF(AND(TODAY()>=DATEVALUE(&quot;28-Nov-2003&quot,TODAY()<=DATEVALUE(&quot;28-Dec-2003&quot),A1,0)

Regards
Ken................

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[Angel79]

Thanks Ken, that works great!

Christa

 

[KenWright]

Regards
Ken...............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[Angel79]

Ok, on this same topic, I have another question (which I previously forgot about once I had the first part of my question answered).

When using
=IF(AND(TODAY()>=DATEVALUE(&quot;28-Nov-2003&quot,TODAY()<=DATEVALUE(&quot;28-Dec-2003&quot),A1,0))

to carry over data from another cell between specific dates, is it possible that once that date passes that the amount that was there last stays in the cell instead of then displaying 0?

Not sure if its even possible or not.

Thanks
Christa

&quot;Merry Christmas and a Happy New Year&quot;