This could get interesting.

I think what you need is a Worksheet Change event to test for the change of
a value in Row 1. When something is changed, I think I'd save that to a
named range and then reference that named range in the formula in Row 8.

In the sheet of interest, right click and view source. Paste this in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RefersToRange As String
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Me.Rows(1)) Then
RefersToRange = "='" & Me.Name & "'!" & Target.Address
Debug.Print RefersToRange

ThisWorkbook.Names.Add Name:="myDate", RefersTo:=RefersToRange
End If

End Sub

Close out the VBE.
In the formula in Row 6, change

"2008/08/02" to myDate
--
HTH,
Barb Reinhardt



"Bahareh" wrote:

Hello
I am trying to create a formula which can look up to find data on a raw and
returns the value on the same column on another raw. Here is the outline of
the table:
A B C D ...... Y

1 3-Aug

2 pj.Month Az Dy Es ...... Ab

3 Month 11/7 12/7 1/21 .... 12/21

4 forcats 36 91 151 ...... 217

5 Adjusted forcasts 70 180 200 ..... 217

6 Actual T.Cum 24 47

7 Actual N.Cum 11 16

8 =INDEX($A$1:$Y$7, MATCH("Actual T.Cum",$A$1:$A$7,),
MATCH(DATEVALUE("2008/08/02"),$A$1:$Y$7,))

The formula is created by Lookup wisard but as you see, a problem exist in
the second MATCH function which have to be updated manually every day within
DATEVALUE function! We actually use formula to get ride of manual updating
but here I have to update the formula daily to find the date which has been
updated on row 1 (which might be in column B to Y and might be today date or
another date).

Please kindly help me to improve the above formula to lookup the updated
date in row 1 and returns the row 6 in the related column.

Great Appreciation previously!
Thanks