Hi Prasad -

Copy this procedure to the worksheet module of the Menu worksheet.

Note that it assumes that the dates in B1:BD1 of the Metrics sheet are
formatted identically to the way they appear in the Calculations sheet
reference column

Private Sub Worksheet_Change(ByVal Target As Range)

'Execute this procedure if a date is entered into C8
'of the "Menu" worksheet
If Not Intersect(Target, Range("C8")) Is Nothing Then

Set wMetrics = Worksheets("Metrics")
Set wMenu = Worksheets("Menu")
Set metricsHeader = wMetrics.Range("B1:BD1")

'Search for matching date and abort if none found
If metricsHeader.Find(wMenu.Range("C8").Value) Is Nothing Then
MsgBox "Date Not Found."
Exit Sub
End If

Set anchorCell = metricsHeader.Find(wMenu.Range("C8").Value)
c = anchorCell.Column
dateformat = anchorCell.NumberFormat

With Worksheets("metrics")
Set formulaCells = Application.Union(.Cells(2, c), _
..Cells(3, c), .Cells(4, c), .Cells(8, c), .Cells(9, c), _
..Cells(10, c), .Cells(15, c), .Cells(16, c), .Cells(21, c), _
..Cells(22, c), .Cells(27, c), .Cells(28, c), .Cells(33, c), _
..Cells(34, c), .Cells(35, c))
End With

'Enter the formula into the 14 destination cells
formulaCells.Formula = "=vlookup(A2 & "" "" & text(" & _
anchorCell.Address & "," & Chr(34) & dateformat & Chr(34) & _
"),Calculations!D$3:$E$36,2,FALSE)"

Target.Select
MsgBox "Formulas updated for " & Target.Value & "."

End If

End Sub
---------
Jay







"EE" wrote:

Hi

Thanks for the help.

In a sheet called "Metrics", I have a matrix where row 1 is dates, So
I have different dates from B1:BD1.

Now I enter a date in one cell in a different sheet. I want a macro to
copy a "dynamic" formula to different cells in the column where it
finds a match of the date I entered.

To give an example. In Sheet "Menu", in C8, I enter a date Jun 12,
2007. Now Jun 12, 2007 is in Cell G1 in "metrics" sheet . When I run
the macro, I want the macro to copy a specified formula, say
"VLOOKUP(""First Column Same Row""&"" ""&"First Row Same Column",
Calculations!R3C4:R36C5,2,FALSE) into cells G2, G3, G4, G8, G9, G10,
G15, G16, G21, G22, G27, G28, G33, G34,G35.

SO the dynamic vlookup formula for G27 will be

Vlookup(A27&" "G1, Calculations $D$3:$E$36,2,FALSE)

for AB22, it will be

Vlookup(A22&" "AB1, Calculations $D$3:$E$36,2,FALSE)
and so on

If June 12, 2007 was in column R (R1, then, the formula is pasted in
Collumn R (with the same row numbers as above.)

I hope I was clear. Thanks in advance for your help.

Best
Prasad