| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
selecting data from a combination of columns & rows.
I have a sheet that has 3 catagories & data listed columns beneath
(B&F,PM & QA), see below AC AD AE AF AG AH AI B&F PM QA .5 door 4.4 .5 door 3.4 1 door 2.4 1 door 4.4 1 door 3.4 2 door 2.4 2 door 4.4 2 door 3.4 air curtain 6.4 3 door 4.4 3 door 3.4 other 4.4 i have created 2 dropdown lists which i select, see below B&F, then select 1 door. excel should then display 4.4 OR QA, then secect 2 door. excel should then display 2.4.etc... A1 A2 A4 Call Type Product Points B&F 1 door 4.4 QA 2 door 2.4 PM FCB 12.4 i'm thinking of a combination HLOOKUP, VLOOKUP & Filtering Can anyone provide a forumla that gives me my required result? Thank You in anticipation. -- Browny |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
selecting data from a combination of columns & rows.
Try this in cell under Points and fill it down as necessary:
=VLOOKUP(B2,OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2),2,FALSE) In OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2) replace 5 by the real number of rows of your 1st table! Regards, Stefi €žBrowny€ť ezt Ă*rta: I have a sheet that has 3 catagories & data listed columns beneath (B&F,PM & QA), see below AC AD AE AF AG AH AI B&F PM QA .5 door 4.4 .5 door 3.4 1 door 2.4 1 door 4.4 1 door 3.4 2 door 2.4 2 door 4.4 2 door 3.4 air curtain 6.4 3 door 4.4 3 door 3.4 other 4.4 i have created 2 dropdown lists which i select, see below B&F, then select 1 door. excel should then display 4.4 OR QA, then secect 2 door. excel should then display 2.4.etc... A1 A2 A4 Call Type Product Points B&F 1 door 4.4 QA 2 door 2.4 PM FCB 12.4 i'm thinking of a combination HLOOKUP, VLOOKUP & Filtering Can anyone provide a forumla that gives me my required result? Thank You in anticipation. -- Browny |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
selecting data from a combination of columns & rows.
Hi Stefi
I have edited my table below to display the correct rows/columns. could you redo the forumla? please -- Browny "Stefi" wrote: Try this in cell under Points and fill it down as necessary: =VLOOKUP(B2,OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2),2,FALSE) In OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2) replace 5 by the real number of rows of your 1st table! Regards, Stefi €žBrowny€ť ezt Ă*rta: I have a sheet that has 3 catagories & data listed columns beneath (B&F,PM & QA), see below AC AD AE AF AG AH AI 2 B&F PM QA 3 .5 door 4.4 .5 door 3.4 1 door 2.4 4 1 door 4.4 1 door 3.4 2 door 2.4 5 2 door 4.4 2 door 3.4 air curtain 6.4 6 3 door 4.4 3 door 3.4 other 4.4 i have created 2 dropdown lists which i select, see below B&F, then select 1 door. excel should then display 4.4 OR QA, then secect 2 door. excel should then display 2.4.etc... L M O Call Type Product Points 12 B&F 1 door 4.4 13 QA 2 door 2.4 14 PM FCB 12.4 i'm thinking of a combination HLOOKUP, VLOOKUP & Filtering Can anyone provide a forumla that gives me my required result? Thank You in anticipation. -- Browny |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
selecting data from a combination of columns & rows.
=VLOOKUP(M12,OFFSET(AD2,0,MATCH(L12,$AD$2:$AH$2,0)-1,5,2),2,FALSE)
€žBrowny€ť ezt Ă*rta: Hi Stefi I have edited my table below to display the correct rows/columns. could you redo the forumla? please -- Browny "Stefi" wrote: Try this in cell under Points and fill it down as necessary: =VLOOKUP(B2,OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2),2,FALSE) In OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2) replace 5 by the real number of rows of your 1st table! Regards, Stefi €žBrowny€ť ezt Ă*rta: I have a sheet that has 3 catagories & data listed columns beneath (B&F,PM & QA), see below AC AD AE AF AG AH AI 2 B&F PM QA 3 .5 door 4.4 .5 door 3.4 1 door 2.4 4 1 door 4.4 1 door 3.4 2 door 2.4 5 2 door 4.4 2 door 3.4 air curtain 6.4 6 3 door 4.4 3 door 3.4 other 4.4 i have created 2 dropdown lists which i select, see below B&F, then select 1 door. excel should then display 4.4 OR QA, then secect 2 door. excel should then display 2.4.etc... L M O Call Type Product Points 12 B&F 1 door 4.4 13 QA 2 door 2.4 14 PM FCB 12.4 i'm thinking of a combination HLOOKUP, VLOOKUP & Filtering Can anyone provide a forumla that gives me my required result? Thank You in anticipation. -- Browny |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
selecting data from a combination of columns & rows.
Hi Stefi
Thanks for that, i have given the wrong impression. from the table of data i want to be able to select B&F, PM or QA, then any of the items below and display the joining points. i think the forumla selects B&F, QA only? -- Browny "Stefi" wrote: =VLOOKUP(M12,OFFSET(AD2,0,MATCH(L12,$AD$2:$AH$2,0)-1,5,2),2,FALSE) €žBrowny€ť ezt Ă*rta: Hi Stefi I have edited my table below to display the correct rows/columns. could you redo the forumla? please -- Browny "Stefi" wrote: Try this in cell under Points and fill it down as necessary: =VLOOKUP(B2,OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2),2,FALSE) In OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2) replace 5 by the real number of rows of your 1st table! Regards, Stefi €žBrowny€ť ezt Ă*rta: I have a sheet that has 3 catagories & data listed columns beneath (B&F,PM & QA), see below AC AD AE AF AG AH AI 2 B&F PM QA 3 .5 door 4.4 .5 door 3.4 1 door 2.4 4 1 door 4.4 1 door 3.4 2 door 2.4 5 2 door 4.4 2 door 3.4 air curtain 6.4 6 3 door 4.4 3 door 3.4 other 4.4 i have created 2 dropdown lists which i select, see below B&F, then select 1 door. excel should then display 4.4 OR QA, then secect 2 door. excel should then display 2.4.etc... L M O Call Type Product Points 12 B&F 1 door 4.4 13 QA 2 door 2.4 14 PM FCB 12.4 i'm thinking of a combination HLOOKUP, VLOOKUP & Filtering Can anyone provide a forumla that gives me my required result? Thank You in anticipation. -- Browny |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
selecting data from a combination of columns & rows.
I checked again, the formula works if tables are located exactly in ranges
you specified. Maybe you are mislead by #NA error in row of PM FCB. FCB doesn't exist in column AF, therefore the formula returns #NA. Regards, Stefi €žBrowny€ť ezt Ă*rta: Hi Stefi Thanks for that, i have given the wrong impression. from the table of data i want to be able to select B&F, PM or QA, then any of the items below and display the joining points. i think the forumla selects B&F, QA only? -- Browny "Stefi" wrote: =VLOOKUP(M12,OFFSET(AD2,0,MATCH(L12,$AD$2:$AH$2,0)-1,5,2),2,FALSE) €žBrowny€ť ezt Ă*rta: Hi Stefi I have edited my table below to display the correct rows/columns. could you redo the forumla? please -- Browny "Stefi" wrote: Try this in cell under Points and fill it down as necessary: =VLOOKUP(B2,OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2),2,FALSE) In OFFSET(AD1,0,MATCH(A2,$AD$1:$AH$1,0)-1,5,2) replace 5 by the real number of rows of your 1st table! Regards, Stefi €žBrowny€ť ezt Ă*rta: I have a sheet that has 3 catagories & data listed columns beneath (B&F,PM & QA), see below AC AD AE AF AG AH AI 2 B&F PM QA 3 .5 door 4.4 .5 door 3.4 1 door 2.4 4 1 door 4.4 1 door 3.4 2 door 2.4 5 2 door 4.4 2 door 3.4 air curtain 6.4 6 3 door 4.4 3 door 3.4 other 4.4 i have created 2 dropdown lists which i select, see below B&F, then select 1 door. excel should then display 4.4 OR QA, then secect 2 door. excel should then display 2.4.etc... L M O Call Type Product Points 12 B&F 1 door 4.4 13 QA 2 door 2.4 14 PM FCB 12.4 i'm thinking of a combination HLOOKUP, VLOOKUP & Filtering Can anyone provide a forumla that gives me my required result? Thank You in anticipation. -- Browny |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| selecting non consectutive cells/rows/columns in excel 2007 | Excel Discussion (Misc queries) | |||
| include more than 3 data series as columns in combination chart | Charts and Charting in Excel | |||
| Selecting All Rows with Certain Data | Excel Discussion (Misc queries) | |||
| Selecting All Rows with Certain Data | Excel Discussion (Misc queries) | |||
| Selecting All Rows with Certain Data | Excel Discussion (Misc queries) | |||
Linear Mode
