Geting a function based on today's date

Posted by David de Carvalho on August 21, 1999 2:18 PM

Hi,

I have an spreadsheet on wich there's one page for
each day of the month.
On the last page I have a formula wich copies the
data from today's page (something like '21'!g13).
Is there a way to automate the daily manually number
change (from '21' to '22' and so on)?

Thanks in advance.
David de Carvalho

Posted by Chris on August 23, 1999 9:58 AM

David,

There's a couple different ways to accomplish this. One would be to use a VBA function that would use relative sheet references. This would work if the last page always wanted to reference the sheet before it. This could be done with the following code:

Function SHTOFF(offset, Ref)
Application.Volatile
SHTOFF = Sheets(Application.Caller.Parent.Index _
+ offset).Range(Ref.Address)
End Function

The worksheet function would be entered as follows:

=SHTOFF(-1,G13)

Your other option would be to just use a worksheet formula like the following:

=INDIRECT("'" & TEXT(NOW(),"dd") & "'!G13")

This will work if you have a sheet for today. If the sheet is for yesterday, you would use:

=INDIRECT("'" & TEXT(NOW()-1,"dd") & "'!G13")

Hope that helps.

Chris

Posted by David de Carvalho on August 25, 1999 5:51 PM

Thanks, Chris, it worked perfectly.

David