Prev: Using SUM + INDEX in array formula
Next: Excell - two conditions count if factors are true when negative nu
From: UKMAN on 6 May 2010 12:57
Max

the actual cell ref for the dbase are

c4:c103 are the names of staff
e3:cz3 is the titles of the skill

the staff individaul skill level for a skill would be the cell in the
releevant row/col i.e. e4 would hold the value.

for the report I select a skill ($n$6) then the names of staff with that
skill are list with their value for that skill i.e.

m9 is the skill value, n9 is the staff name.

hope this shows it for you.

many thanks for your patience.

UKMAN




"Max" wrote:

> Do not send. You could paste a representative sample data/layout here in
> plain text.
> --
> Max
> Singapore
> ---
From: Max on 6 May 2010 21:19
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 7 May 2010 03:53
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 7 May 2010 11:36
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 10 May 2010 11:30
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  |  Next  |  Last
Pages: 1 2 3
Prev: Using SUM + INDEX in array formula
Next: Excell - two conditions count if factors are true when negative nu