Lookup formula & an error to find the match

[Bahareh]

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

 

[Rick Rothstein \(MVP - VB\)]

I think you are going to have to tell us more about Row 1 between Columns B
and Y. Is the updated date that is entered there **always** going to be the
latest of any of the dates in that row? If not, is there anything about the
entry that would make it "stand out" so we can identify it?

Rick

 

[Barb Reinhardt]

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

 

[Bahareh]

Thank for your attention. I've created a spreadsheet view via following site
for better view.

http://sheet.zoho.com/public/seasoning/question-on-community

please kindly look at it to feel my explanations.
for example, today I updated row I6 (value=67) and put the date of update on
row 1 as 3-Aug. Tomorrow, I might have an update or not. if I have an update,
I will do the same as today and put in I1 as 4-Aug. if not I will make no
changes. Once month ends I will delete entry in I1 and I will put the date
of update in J1 and I will update the row 6 in column J (so j6 will be
updated during next month). So actually all cells in the row 1 are empty,
except the cell above the current month (which is moving forward during the
year).

Hope I could explain it better. Thank you again.

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure I completely understand how you are using this worksheet, but I
**think** you are saying that there will **always** be only be one date
listed in Row 1 with the rest of the cells in Row 1 (except perhaps A1)
being empty. If that is the case, this formula should work for you...

=INDEX($A$1:$Y$7,MATCH("Actual
T.Cum",$A$1:$A$7,0),MATCH(LOOKUP(2,1/(A1:Y1<>""),1:1),1:1,0))

If I have misunderstood you, and there can be more than one date in Row 1,
then tell us if the date you will want to find is always the latest of the
dates listed.

Rick

 

[Bahareh]

Dear Rick
Thanks. You got my purpose correctly. and I think your answer can solve my
problem. but unfortunately I just know about lookup function in simple cases
and I don't understand your formula well so I can't adjust it to my original
spreadsheet which has other tables similarly. Please kindly can you explain
the meaning of each part? I guessed in
"MATCH(LOOKUP(2,1/(A1:Y1<>""),1:1),1:1,0)",the number "2" means that it will
lookup from second column and (A1:Y1<>"") means to find the cell in first row
which is not empty. but I don't understand 1/(A1:Y1<>"") and 1:1 and last
1:1,0


Thanks again.
P.S. I afraid if I never learn lookup functions perfect

 

[Bahareh]

Thanks Barb. Unfortunately I couldn't try your solution as I don't know much
about VBA and your codes requires to be adjusted with my original spreadsheet
names and settings. but I appreciate your care. and wish you the best in
reverse.

 

[Bahareh]

I could find the logic of it. Now it works perfects. Thank you Rick again.