A template is good, but it's much more friendly to let the user input
the date any way s/he wants, and have VBA do the work:

Public Sub Enter_Formula_In_B7()
Const sDATE_FORMAT = "dd MMM yyyy"
Const sTWO_LINES As String = vbNewLine & vbNewLine
Const sMSG1 As String = "Enter date of sheet to reference:"
Const sMSG2 As String = "Reference must be a date!" & sTWO_LINES
Const sMSG3 As String = "Sheet '%%' does not exist!" & sTWO_LINES
Const sFORMULA As String = _
"=VLOOKUP($A7,'%%'!$A$3:$C$97,2,FALSE)"
Const sTITLE As String = "Enter Formula"
Const nSTRING_TYPE As Long = 2
Dim vInput As Variant
Dim wsRef As Worksheet
Dim sDefault As String
Dim sMsg As String
Dim sDate As String

sMsg = sMSG1
sDefault = Format(Date, sDATE_FORMAT)
Do
vInput = Application.InputBox( _
Prompt:=sMsg, _
Title:=sTITLE, _
Default:=sDefault, _
Type:=nSTRING_TYPE)
If vInput = False Then Exit Sub 'User cancelled
If IsDate(vInput) Then
sDate = Format(vInput, sDATE_FORMAT)
On Error Resume Next
Set wsRef = Sheets(sDate)
On Error GoTo 0
If wsRef Is Nothing Then sMsg = _
Application.Substitute(sMSG3, "%%", sDate) & sMSG1
Else
sMsg = sMSG2 & sMSG1
End If
Loop Until Not wsRef Is Nothing
Range("B7").Formula = _
Application.Substitute(sFORMULA, "%%", sDate)
End Sub

Change the format (sDATE_FORMAT) as desired...


In article ,
"JulieD" wrote:

my problem with this is that sheet names can't contain / so if the person
enters
1/1/05 in response to this question:
shtdate = InputBox("Enter date of sheet to reference")
you'll have a problem.

maybe putting an example of how you want the data entered (or a default
value) might be useful?
e.g.
shtdate = Inputbox("Enter date of sheet to reference, e.g. 010105")
or
shtdate = Inputbox("Enter date of sheet to reference","Sheet Date","010105")