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

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


"Bahareh" wrote in message
...
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

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

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

http://sheet.zoho.com/public/seasoni...n-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)" wrote:

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


"Bahareh" wrote in message
...
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

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" wrote in message
...
Thank for your attention. I've created a spreadsheet view via following
site
for better view.

http://sheet.zoho.com/public/seasoni...n-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)" wrote:

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


"Bahareh" wrote in message
...
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

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 :(

"Rick Rothstein (MVP - VB)" wrote:

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" wrote in message
...
Thank for your attention. I've created a spreadsheet view via following
site
for better view.

http://sheet.zoho.com/public/seasoni...n-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)" wrote:

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


"Bahareh" wrote in message
...
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

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.

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