formula to return value from a matrix - I know hor and vert values

[UKMAN]

hi

Filtering is not an option
I have a table that shows skill titles across the columns e3:cz3 and staff
names down c4:c103, for each name and skill there will be a value between 1 &
4 see example below

C E

Name Excel

Colin 3

I need to insert the finding into a bespoke report that if I select a skill
(from a drop down list) the report will list the names and associated value

Skill title drop down is k5

name is k8, skill value is j8

Cheers in advance

 

[Max]

Indicatively, perhaps a basic Index(area, match(row),match(col))
Something like this:
=Index(e4:cz103,match(k8,c4:c103,0),match(k5,e3:cz3,0))
I'm not sure where your 3rd variable (skill value j8) comes in though, w/o a
clearer picture of your sample data layout. Give it a try 1st ..

 

[UKMAN]

Max,

many thanks the 3rd value is manually input to show the skill level of the
name against the skill title.

Bearing in mind there will be many names with the skill do I just copy the
below down x amount of rows?

cheers

UKMAN

 

[Max]

Bearing in mind there will be many names with the skill do I just copy the

below down x amount of rows?

Just fix (with $ signs) the cell refs for Area, row array, col array, skill
title dropdown cell in the earlier, ie like this:
=Index($e$4:$cz$103,match(k8,$c$4:$c$103,0),match($k$5,$e$3:$cz$3,0))
then you should be able to copy down

 

[UKMAN]

Max,

Hope you do mnot mind but I have sent you a cutdown version of the file and
on the question tab have put some notes.

Reason being I don't think I have explained my layout / problem correctly.

Cheers

UKMAN

 

[UKMAN]

Max,

I got a bounce back on the email address in your profile

mine is (e-mail address removed) if you want to keep yours private.

cheers

 

[Max]

Do not send. You could paste a representative sample data/layout here in
plain text.

 

[UKMAN]

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]

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

 

[UKMAN]

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

 

[Don Guillett]

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.

 

[UKMAN]

Don,

I have sent the email with an attachment and test data inserted. many thanks

UKMAN

 

[Don Guillett]

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

 

[UKMAN]

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]

Code usually refers to macros,NOT formulas. The "code" below was in the
sheet module of the file I sent to you.