(Msg. 1) Posted: Sun Aug 03, 2008 11:33 am
Post subject: Lookup formula & an error to find the match Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
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
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.
(Msg. 2) Posted: Sun Aug 03, 2008 11:57 am
Post subject: RE: Lookup formula & an error to find the match [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
(Msg. 3) Posted: Sun Aug 03, 2008 2:44 pm
Post subject: Re: Lookup formula & an error to find the match [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <Bahareh.RemoveThis@discussions.microsoft.com> wrote in message
news:E858CE10-D585-4AFC-BB20-7BAA364EEBEF@microsoft.com...
> 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
(Msg. 4) Posted: Sun Aug 03, 2008 2:44 pm
Post subject: Re: Lookup formula & an error to find the match [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thank for your attention. I've created a spreadsheet view via following site
for better view.
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" <Bahareh.DeleteThis@discussions.microsoft.com> wrote in message
> news:E858CE10-D585-4AFC-BB20-7BAA364EEBEF@microsoft.com...
> > 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
>
>
(Msg. 5) Posted: Sun Aug 03, 2008 4:54 pm
Post subject: Re: Lookup formula & an error to find the match [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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...
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" <Bahareh.DeleteThis@discussions.microsoft.com> wrote in message
news:73B2B2B4-207A-4F05-8344-C05EF7D1CFA3@microsoft.com...
> 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)" 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" <Bahareh.DeleteThis@discussions.microsoft.com> wrote in message
>> news:E858CE10-D585-4AFC-BB20-7BAA364EEBEF@microsoft.com...
>> > 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
>>
>>
(Msg. 6) Posted: Mon Aug 04, 2008 1:11 am
Post subject: Re: Lookup formula & an error to find the match [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <Bahareh.RemoveThis@discussions.microsoft.com> wrote in message
> news:73B2B2B4-207A-4F05-8344-C05EF7D1CFA3@microsoft.com...
> > 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)" 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" <Bahareh.RemoveThis@discussions.microsoft.com> wrote in message
> >> news:E858CE10-D585-4AFC-BB20-7BAA364EEBEF@microsoft.com...
> >> > 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
> >>
> >>
>
>
(Msg. 7) Posted: Mon Aug 04, 2008 1:20 am
Post subject: RE: Lookup formula & an error to find the match [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
(Msg. 8) Posted: Fri Aug 08, 2008 4:36 am
Post subject: Re: Lookup formula & an error to find the match [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I could find the logic of it. Now it works perfects. Thank you Rick again.
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office