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 09:53
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" wrote:

> 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 ..
> --
> Max
> Singapore
> ---
> "UKMAN" wrote:
> > 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
From: Max on 6 May 2010 10:15
> 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
--
Max
Singapore
---

From: UKMAN on 6 May 2010 10:50
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

"Max" wrote:

> > 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
> --
> Max
> Singapore
> ---
>
From: UKMAN on 6 May 2010 11:21
Max,

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

mine is ukman1(a)hotmail.com if you want to keep yours private.

cheers

"Max" wrote:

> > 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
> --
> Max
> Singapore
> ---
>
From: Max on 6 May 2010 12:10
Do not send. You could paste a representative sample data/layout here in
plain text.
--
Max
Singapore
---
 |  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