Prev: table lookup
Next: Conditional Formatting and Formulas
From: DubboPete on 9 May 2010 20:51
Hi Folks,

I have a sheet, where the contents of cell A1 is a result of other
criteria being met. For the purpose of this example, the result in A1
is "Oral Health"

In cells B1 thru to W1 are column headers, one of which will match the
result in A1 (Oral Health is E1, Population Health is K1, etc).
Under each of these header cells are seven or eight cells with a range
of vehicles (B2-B9 thru W2-W9 respectively)

What I am trying to do is create a custom list of vehicles so that if
A1 = Oral Health, and the Oral Health header is E1, I'd like the
contents of E2 - E9 to fill cells A2 to A9. That way the Oral Health
vehicles become the focus of a drop down box on another related sheet.

Everything but this lookup reference is working, and not sure how to
apply the correct lookup.

Any help greatly appreciated

TIA

Pete
From: T. Valko on 9 May 2010 22:29
Try this...

Entered in A2 and copied down to A9:

=INDEX(B$2:W$9,ROWS(A$2:A2),MATCH(A$1,B$1:W$1,0))

--
Biff
Microsoft Excel MVP


"DubboPete" <osnabruc(a)tpg.com.au> wrote in message
news:e0def0c5-dc4e-485f-81a4-5ec8de0b740d(a)m31g2000pre.googlegroups.com...
> Hi Folks,
>
> I have a sheet, where the contents of cell A1 is a result of other
> criteria being met. For the purpose of this example, the result in A1
> is "Oral Health"
>
> In cells B1 thru to W1 are column headers, one of which will match the
> result in A1 (Oral Health is E1, Population Health is K1, etc).
> Under each of these header cells are seven or eight cells with a range
> of vehicles (B2-B9 thru W2-W9 respectively)
>
> What I am trying to do is create a custom list of vehicles so that if
> A1 = Oral Health, and the Oral Health header is E1, I'd like the
> contents of E2 - E9 to fill cells A2 to A9. That way the Oral Health
> vehicles become the focus of a drop down box on another related sheet.
>
> Everything but this lookup reference is working, and not sure how to
> apply the correct lookup.
>
> Any help greatly appreciated
>
> TIA
>
> Pete


From: DubboPete on 9 May 2010 23:24
On May 10, 12:29 pm, "T. Valko" <biffinp...(a)comcast.net> wrote:
> Try this...
>
> Entered in A2 and copied down to A9:
>
> =INDEX(B$2:W$9,ROWS(A$2:A2),MATCH(A$1,B$1:W$1,0))
>
> --
> Biff
> Microsoft Excel MVP
>
> "DubboPete" <osnab...(a)tpg.com.au> wrote in message
>
> news:e0def0c5-dc4e-485f-81a4-5ec8de0b740d(a)m31g2000pre.googlegroups.com...
>
>
>
> > Hi Folks,
>
> > I have a sheet, where the contents of cell A1 is a result of other
> > criteria being met.  For the purpose of this example, the result in A1
> > is "Oral Health"
>
> > In cells B1 thru to W1 are column headers, one of which will match the
> > result in A1 (Oral Health is E1, Population Health is K1, etc).
> > Under each of these header cells are seven or eight cells with a range
> > of vehicles (B2-B9 thru W2-W9 respectively)
>
> > What I am trying to do is create a custom list of vehicles so that if
> > A1 = Oral Health, and the Oral Health header is E1, I'd like the
> > contents of E2 - E9 to fill cells A2 to A9.  That way the Oral Health
> > vehicles become the focus of a drop down box on another related sheet.
>
> > Everything but this lookup reference is working, and not sure how to
> > apply the correct lookup.
>
> > Any help greatly appreciated
>
> > TIA
>
> > Pete- Hide quoted text -
>
> - Show quoted text -

Thanks Biff
works a treat
job fixed!

Pete
From: T. Valko on 10 May 2010 00:36
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"DubboPete" <osnabruc(a)tpg.com.au> wrote in message
news:6eb6ff45-1a15-494e-9c5d-155f66f7d307(a)k17g2000pro.googlegroups.com...
On May 10, 12:29 pm, "T. Valko" <biffinp...(a)comcast.net> wrote:
> Try this...
>
> Entered in A2 and copied down to A9:
>
> =INDEX(B$2:W$9,ROWS(A$2:A2),MATCH(A$1,B$1:W$1,0))
>
> --
> Biff
> Microsoft Excel MVP
>
> "DubboPete" <osnab...(a)tpg.com.au> wrote in message
>
> news:e0def0c5-dc4e-485f-81a4-5ec8de0b740d(a)m31g2000pre.googlegroups.com...
>
>
>
> > Hi Folks,
>
> > I have a sheet, where the contents of cell A1 is a result of other
> > criteria being met. For the purpose of this example, the result in A1
> > is "Oral Health"
>
> > In cells B1 thru to W1 are column headers, one of which will match the
> > result in A1 (Oral Health is E1, Population Health is K1, etc).
> > Under each of these header cells are seven or eight cells with a range
> > of vehicles (B2-B9 thru W2-W9 respectively)
>
> > What I am trying to do is create a custom list of vehicles so that if
> > A1 = Oral Health, and the Oral Health header is E1, I'd like the
> > contents of E2 - E9 to fill cells A2 to A9. That way the Oral Health
> > vehicles become the focus of a drop down box on another related sheet.
>
> > Everything but this lookup reference is working, and not sure how to
> > apply the correct lookup.
>
> > Any help greatly appreciated
>
> > TIA
>
> > Pete- Hide quoted text -
>
> - Show quoted text -

Thanks Biff
works a treat
job fixed!

Pete


 | 
Pages: 1
Prev: table lookup
Next: Conditional Formatting and Formulas