Prev: Using SUM + INDEX in array formula
Next: Excell - two conditions count if factors are true when negative nu
From: Don Guillett on 11 May 2010 11:52

Private Sub Worksheet_Change(ByVal Target As Range) 'SalesAidSoftware
Dim dc As Long
Dim mc As Long
Dim lr As Long
Dim lc As Long
Dim mwhat As String
If Not Intersect(Target, Range("e8,e10,h8,h10")) Is Nothing Then
mwhat = Target
dc = ActiveSheet.Rows(6).Find(mwhat, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Column
'MsgBox dc
With Sheets("Dbase")
mc = .Rows(3).Find(mwhat, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Column
'MsgBox mc
lr = .Cells(Rows.Count, 1).End(xlUp).Row
lc = .Cells(3, Columns.Count).End(xlToLeft).Column
'MsgBox lr
'MsgBox lc
..Range(.Cells(3, 1), .Cells(lr, lc)).AutoFilter Field:=mc, Criteria1:="<>"
..Cells(4, "c").Resize(lr).Copy Cells(9, dc)
..Cells(4, mc).Resize(lr).Copy Cells(9, dc - 1)
..Range(.Cells(3, 1), .Cells(lr, lc)).AutoFilter
End With
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"UKMAN" <ukman1(a)hotmail.com.(donotspam)> wrote in message
news:A8CDFC58-D428-4A26-AF94-6402A7A38632(a)microsoft.com...
> Don,
>
> I have sent the email with an attachment and test data inserted. many
> thanks
>
> UKMAN
>
> "Don Guillett" wrote:
>
>> If desired, send your file to my address below. I will only look
>> if:
>> 1. You send a copy of this message on an inserted sheet
>> 2. You give me the newsgroup and the subject line
>> 3. You send a clear explanation of what you want
>> 4. You send before/after examples and expected results.
>>
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett(a)gmail.com
>> "UKMAN" <ukman1(a)hotmail.com.(donotspam)> wrote in message
>> news:A4E14335-95CE-42C5-9F84-0AFD7B9BEBEF(a)microsoft.com...
>> > Max, I am off line today and will try this but many thanks and maybe it
>> > is
>> > the way I am expalining it hence why I was going to send you the file.
>> >
>> > The table is just a matrix of skill titles across the columns i.e
>> > "excel"
>> > "Word" etc, and staff names listed down the first column i.e. "Colin"
>> > "Fred"
>> > and the intersecting cell will show a value (i.e. 2,1) that expresses
>> > the
>> > knowledge level the staff name has for that skill. This value is
>> > manally
>> > put
>> > in by me when populating the matrix.
>> >
>> > My report simply extracts and lists all the names and the value that
>> > have
>> > the skill that is selected from the drop down box.
>> >
>> > I can do a vlookup to match the skill and name but I do not know how I
>> > would
>> > select the correct intersecting cell to select the skill value?? :(
>> >
>> > I am very grateful for your help
>> >
>> > UKMAN
>> >
>> >
>> >
>> >
>> > "Max" wrote:
>> >
>> >> I'm afraid its still not clear to me. Anyway, here's a deeper thought
>> >> hazarded
>> >>
>> >> Assume you do have a 3rd var, ie my previous line applies
>> >> > .. your 3rd variable (skill value j8) ...
>> >>
>> >> Assume you have this kind of layout
>> >> col k....col j
>> >> name1 2
>> >> name1 3
>> >> etc
>> >>
>> >> where col k = names, col j = skill values
>> >> where the association is clear considering the 2 cols together
>> >>
>> >> Assume in your ref table, $d$4:$d$103 is where the skill values are
>> >> found
>> >> Based on the above, you could try changing this "match(row)" term in
>> >> the
>> >> earlier index/match:
>> >> match(k8,$c$4:$c$103,0)
>> >>
>> >> to this:
>> >> match(1,index((k8=$c$4:$c$103,0)*(j8=$d$4:$d$103),),0)
>> >>
>> >> Hence the earlier expression:
>> >> =Index($e$4:$cz$103,match(k8,$c$4:$c$103,0),match($k$5,$e$3:$cz$3,0))
>> >>
>> >> will become (untested):
>> >> =Index($e$4:$cz$103,match(1,index((k8=$c$4:$c$103,0)*(j8=$d$4:$d$103),),0),match($k$5,$e$3:$cz$3,0))
>> >> Just press normal ENTER to confirm the entire formula, and you should
>> >> be
>> >> able to copy it down
>> >> --
>> >> Max
>> >> Singapore
>> >> ---
>>
>> .
>>

From: UKMAN on 12 May 2010 07:57
Don,

I recieved your file and replied as there was no code just the values copied??

Not sure what you want me to do with below as I am a beginner trying to
learn :)

Cheers

UKMAN

"Don Guillett" wrote:

>
> Private Sub Worksheet_Change(ByVal Target As Range) 'SalesAidSoftware
> Dim dc As Long
> Dim mc As Long
> Dim lr As Long
> Dim lc As Long
> Dim mwhat As String
> If Not Intersect(Target, Range("e8,e10,h8,h10")) Is Nothing Then
> mwhat = Target
> dc = ActiveSheet.Rows(6).Find(mwhat, LookIn:=xlValues, _
> LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
> _
> MatchCase:=False, SearchFormat:=False).Column
> 'MsgBox dc
> With Sheets("Dbase")
> mc = .Rows(3).Find(mwhat, LookIn:=xlValues, _
> LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
> _
> MatchCase:=False, SearchFormat:=False).Column
> 'MsgBox mc
> lr = .Cells(Rows.Count, 1).End(xlUp).Row
> lc = .Cells(3, Columns.Count).End(xlToLeft).Column
> 'MsgBox lr
> 'MsgBox lc
> ..Range(.Cells(3, 1), .Cells(lr, lc)).AutoFilter Field:=mc, Criteria1:="<>"
> ..Cells(4, "c").Resize(lr).Copy Cells(9, dc)
> ..Cells(4, mc).Resize(lr).Copy Cells(9, dc - 1)
> ..Range(.Cells(3, 1), .Cells(lr, lc)).AutoFilter
> End With
> End If
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "UKMAN" <ukman1(a)hotmail.com.(donotspam)> wrote in message
> news:A8CDFC58-D428-4A26-AF94-6402A7A38632(a)microsoft.com...
> > Don,
> >
> > I have sent the email with an attachment and test data inserted. many
> > thanks
> >
> > UKMAN
> >
> > "Don Guillett" wrote:
> >
> >> If desired, send your file to my address below. I will only look
> >> if:
> >> 1. You send a copy of this message on an inserted sheet
> >> 2. You give me the newsgroup and the subject line
> >> 3. You send a clear explanation of what you want
> >> 4. You send before/after examples and expected results.
> >>
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> dguillett(a)gmail.com
> >> "UKMAN" <ukman1(a)hotmail.com.(donotspam)> wrote in message
> >> news:A4E14335-95CE-42C5-9F84-0AFD7B9BEBEF(a)microsoft.com...
> >> > Max, I am off line today and will try this but many thanks and maybe it
> >> > is
> >> > the way I am expalining it hence why I was going to send you the file.
> >> >
> >> > The table is just a matrix of skill titles across the columns i.e
> >> > "excel"
> >> > "Word" etc, and staff names listed down the first column i.e. "Colin"
> >> > "Fred"
> >> > and the intersecting cell will show a value (i.e. 2,1) that expresses
> >> > the
> >> > knowledge level the staff name has for that skill. This value is
> >> > manally
> >> > put
> >> > in by me when populating the matrix.
> >> >
> >> > My report simply extracts and lists all the names and the value that
> >> > have
> >> > the skill that is selected from the drop down box.
> >> >
> >> > I can do a vlookup to match the skill and name but I do not know how I
> >> > would
> >> > select the correct intersecting cell to select the skill value?? :(
> >> >
> >> > I am very grateful for your help
> >> >
> >> > UKMAN
> >> >
> >> >
> >> >
> >> >
> >> > "Max" wrote:
> >> >
> >> >> I'm afraid its still not clear to me. Anyway, here's a deeper thought
> >> >> hazarded
> >> >>
> >> >> Assume you do have a 3rd var, ie my previous line applies
> >> >> > .. your 3rd variable (skill value j8) ...
> >> >>
> >> >> Assume you have this kind of layout
> >> >> col k....col j
> >> >> name1 2
> >> >> name1 3
> >> >> etc
> >> >>
> >> >> where col k = names, col j = skill values
> >> >> where the association is clear considering the 2 cols together
> >> >>
> >> >> Assume in your ref table, $d$4:$d$103 is where the skill values are
> >> >> found
> >> >> Based on the above, you could try changing this "match(row)" term in
> >> >> the
> >> >> earlier index/match:
> >> >> match(k8,$c$4:$c$103,0)
> >> >>
> >> >> to this:
> >> >> match(1,index((k8=$c$4:$c$103,0)*(j8=$d$4:$d$103),),0)
> >> >>
> >> >> Hence the earlier expression:
> >> >> =Index($e$4:$cz$103,match(k8,$c$4:$c$103,0),match($k$5,$e$3:$cz$3,0))
> >> >>
> >> >> will become (untested):
> >> >> =Index($e$4:$cz$103,match(1,index((k8=$c$4:$c$103,0)*(j8=$d$4:$d$103),),0),match($k$5,$e$3:$cz$3,0))
> >> >> Just press normal ENTER to confirm the entire formula, and you should
> >> >> be
> >> >> able to copy it down
> >> >> --
> >> >> Max
> >> >> Singapore
> >> >> ---
> >>
> >> .
> >>
>
> .
>
From: Don Guillett on 12 May 2010 08:19
Code usually refers to macros,NOT formulas. The "code" below was in the
sheet module of the file I sent to you.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"UKMAN" <ukman1(a)hotmail.com.(donotspam)> wrote in message
news:A8DE8736-ADE7-4DEB-B5A3-45A21C654003(a)microsoft.com...
> Don,
>
> I recieved your file and replied as there was no code just the values
> copied??
>
> Not sure what you want me to do with below as I am a beginner trying to
> learn :)
>
> Cheers
>
> UKMAN
>
> "Don Guillett" wrote:
>
>>
>> Private Sub Worksheet_Change(ByVal Target As Range) 'SalesAidSoftware
>> Dim dc As Long
>> Dim mc As Long
>> Dim lr As Long
>> Dim lc As Long
>> Dim mwhat As String
>> If Not Intersect(Target, Range("e8,e10,h8,h10")) Is Nothing Then
>> mwhat = Target
>> dc = ActiveSheet.Rows(6).Find(mwhat, LookIn:=xlValues, _
>> LookAt:=xlWhole, SearchOrder:=xlByColumns,
>> SearchDirection:=xlNext,
>> _
>> MatchCase:=False, SearchFormat:=False).Column
>> 'MsgBox dc
>> With Sheets("Dbase")
>> mc = .Rows(3).Find(mwhat, LookIn:=xlValues, _
>> LookAt:=xlWhole, SearchOrder:=xlByColumns,
>> SearchDirection:=xlNext,
>> _
>> MatchCase:=False, SearchFormat:=False).Column
>> 'MsgBox mc
>> lr = .Cells(Rows.Count, 1).End(xlUp).Row
>> lc = .Cells(3, Columns.Count).End(xlToLeft).Column
>> 'MsgBox lr
>> 'MsgBox lc
>> ..Range(.Cells(3, 1), .Cells(lr, lc)).AutoFilter Field:=mc,
>> Criteria1:="<>"
>> ..Cells(4, "c").Resize(lr).Copy Cells(9, dc)
>> ..Cells(4, mc).Resize(lr).Copy Cells(9, dc - 1)
>> ..Range(.Cells(3, 1), .Cells(lr, lc)).AutoFilter
>> End With
>> End If
>> End Sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> dguillett(a)gmail.com
>> "UKMAN" <ukman1(a)hotmail.com.(donotspam)> wrote in message
>> news:A8CDFC58-D428-4A26-AF94-6402A7A38632(a)microsoft.com...
>> > Don,
>> >
>> > I have sent the email with an attachment and test data inserted. many
>> > thanks
>> >
>> > UKMAN
>> >
>> > "Don Guillett" wrote:
>> >
>> >> If desired, send your file to my address below. I will only look
>> >> if:
>> >> 1. You send a copy of this message on an inserted sheet
>> >> 2. You give me the newsgroup and the subject line
>> >> 3. You send a clear explanation of what you want
>> >> 4. You send before/after examples and expected results.
>> >>
>> >>
>> >> --
>> >> Don Guillett
>> >> Microsoft MVP Excel
>> >> SalesAid Software
>> >> dguillett(a)gmail.com
>> >> "UKMAN" <ukman1(a)hotmail.com.(donotspam)> wrote in message
>> >> news:A4E14335-95CE-42C5-9F84-0AFD7B9BEBEF(a)microsoft.com...
>> >> > Max, I am off line today and will try this but many thanks and maybe
>> >> > it
>> >> > is
>> >> > the way I am expalining it hence why I was going to send you the
>> >> > file.
>> >> >
>> >> > The table is just a matrix of skill titles across the columns i.e
>> >> > "excel"
>> >> > "Word" etc, and staff names listed down the first column i.e.
>> >> > "Colin"
>> >> > "Fred"
>> >> > and the intersecting cell will show a value (i.e. 2,1) that
>> >> > expresses
>> >> > the
>> >> > knowledge level the staff name has for that skill. This value is
>> >> > manally
>> >> > put
>> >> > in by me when populating the matrix.
>> >> >
>> >> > My report simply extracts and lists all the names and the value that
>> >> > have
>> >> > the skill that is selected from the drop down box.
>> >> >
>> >> > I can do a vlookup to match the skill and name but I do not know how
>> >> > I
>> >> > would
>> >> > select the correct intersecting cell to select the skill value?? :(
>> >> >
>> >> > I am very grateful for your help
>> >> >
>> >> > UKMAN
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > "Max" wrote:
>> >> >
>> >> >> I'm afraid its still not clear to me. Anyway, here's a deeper
>> >> >> thought
>> >> >> hazarded
>> >> >>
>> >> >> Assume you do have a 3rd var, ie my previous line applies
>> >> >> > .. your 3rd variable (skill value j8) ...
>> >> >>
>> >> >> Assume you have this kind of layout
>> >> >> col k....col j
>> >> >> name1 2
>> >> >> name1 3
>> >> >> etc
>> >> >>
>> >> >> where col k = names, col j = skill values
>> >> >> where the association is clear considering the 2 cols together
>> >> >>
>> >> >> Assume in your ref table, $d$4:$d$103 is where the skill values are
>> >> >> found
>> >> >> Based on the above, you could try changing this "match(row)" term
>> >> >> in
>> >> >> the
>> >> >> earlier index/match:
>> >> >> match(k8,$c$4:$c$103,0)
>> >> >>
>> >> >> to this:
>> >> >> match(1,index((k8=$c$4:$c$103,0)*(j8=$d$4:$d$103),),0)
>> >> >>
>> >> >> Hence the earlier expression:
>> >> >> =Index($e$4:$cz$103,match(k8,$c$4:$c$103,0),match($k$5,$e$3:$cz$3,0))
>> >> >>
>> >> >> will become (untested):
>> >> >> =Index($e$4:$cz$103,match(1,index((k8=$c$4:$c$103,0)*(j8=$d$4:$d$103),),0),match($k$5,$e$3:$cz$3,0))
>> >> >> Just press normal ENTER to confirm the entire formula, and you
>> >> >> should
>> >> >> be
>> >> >> able to copy it down
>> >> >> --
>> >> >> Max
>> >> >> Singapore
>> >> >> ---
>> >>
>> >> .
>> >>
>>
>> .
>>

First  |  Prev  | 
Pages: 1 2 3
Prev: Using SUM + INDEX in array formula
Next: Excell - two conditions count if factors are true when negative nu