Date Formula

[Guest]

I have a column of dates in Column A that generate with a formula like so:
A1 "=O18" (This is currently a date that is inputted by the user.
a2 "=b1+1"

Column b is like:
b1 "=a1+13"
b2 "=a2+13"

(Quotes are used here to indicate the formula I am using in the cells)
These formulas go down about 57 cells. I would like to automate this so
that when the last cell is equal to (Or greater than) today, cell A1 will
automatically change to the date in the last cell. Any help in how to do this
would be greatly appreciated!

Thanks,
Tim

 

[Guest]

Hi Tim,

I don't think formulas will solve this - you'd just end up with a circular
reference (if I've understood this correctly).

Ultimately, your best solution will be to use some VBA (macro language) to
automate this. Unfortunately, I'm pants at VBA so my best suggestion is that
you bung your message to the "Excel - Programming" board and you'll probably
find people more qualified to help there.

Alternatively, if I've missed the point then apologies!

 

[Guest]

Thanks "mr tom",
I was hoping that there would be a formula solution to this, but it looks
like there isn't so since I'm "pants" at VBA I'll take my chances at not
understanding the answer I may get from the programming board!

Thanks for the reply, it was mychly appreciated.

-Tim

 

[Guest]

They're generally pretty good on the programming board. They'll write some
code for you (a complete subroutine).

All you'll need to do is open the code window (Alt-F11), select your
worksheet and paste in the code.

The name of the subroutine (or macro) is then visible whenever you go to the
"run macro" screen.

Unless you call the macro Autoexec, then you'll need to run it manually.

HTH