I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

G1: holds drop down menu

=INDEX(E1:E7,MAX((A1:A7=G1)*(ROW(A1:A7))))

ctrl+shift+enter, not just enter


"liem" wrote:

I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

Just apply and use autofilter's drop list. It'll return exactly what you're
after.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"liem" wrote:
I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

Just try this formula
=IF(ROWS($1:1)<=COUNTIF($A$2:$A$16,$G$1),INDEX(B$2 :B$16,SMALL(IF(($A$2:$A$16=$G$1),ROW(B$2:B$16)-MIN(ROW(B$2:B$16))+1),ROWS($1:1))),"")
G1 = merchandise
With regards
Sridhar

"liem" wrote:

I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

I tried but how come it began on the second person on the list ( Jeff) and
it skip the first person
--
thanks


"yshridhar" wrote:

Just try this formula
=IF(ROWS($1:1)<=COUNTIF($A$2:$A$16,$G$1),INDEX(B$2 :B$16,SMALL(IF(($A$2:$A$16=$G$1),ROW(B$2:B$16)-MIN(ROW(B$2:B$16))+1),ROWS($1:1))),"")
G1 = merchandise
With regards
Sridhar

"liem" wrote:

I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

Try this:

Assume your data is in the range A2:E8

Drop down in cell A11

Enter this formula in cell B11 and copy across to E11:

=IF(ROWS(B$11:B11)<=COUNTIF($A$2:$A$8,$A$11),INDEX (B$2:B$8,MATCH($A$11,$A$2:$A$8,0)+ROWS(B$11:B11)-1),"")

Select the range B11:E11 and copy down to a number of rows that is equal to
the maximum count of any single selection in your drop down list. For
example, "merchandise" appears the most times in your table. It appears 3
times so you have to copy the formulas to at least 3 rows.

Screencap:

http://img107.imageshack.us/img107/6554/lookupuy0.jpg

--
Biff
Microsoft Excel MVP


"liem" wrote in message
...
I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

Hi

It looks like your sourse table will be grouped by items. When this is the
case, then let's assume your source table is on sheet Sheet1, with headers
in row 1, and that the output table will be on some another sheet, with
headers (for name and figure columns) there in row 3 and data validation
list dropdown in cell A1.

1. Define the cell A1 on output sheet sa a named range (Selection in my
example here);
2. For sheet Sheet1 define a dynamic named range (Item as an example)
Item=OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1,1);
3. Define named ranges
SelectRow1=MATCH(Selection,Item,0)
SelectCnt=COUNTIF(Item,Selection)
SelectRng=OFFSET(Sheet1!$A$1,SelectRow1,,SelectCnt ,5);
4. On output sheet, into call A4 enter the formula
=IF(ISERROR(INDEX(SelectRng,ROW()-ROW(A$3),COLUMN()+1)),"",INDEX(SelectRng,ROW()-ROW(A$3),COLUMN()+1))
, and copy it into range A4:D4;
5. Copy the range A4:D4 down for as much rows as you estimate you'll need to
display all rows for any item.

It's done! Select an item in cell A1 on output sheet, and according rows are
displayed. Don't forget - whenever you add data into source sheet, sort the
table by items before processing the report.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"liem" wrote in message
...
I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

I tried this but on step # 4 after i finished input data it has a blank cell
with no DATA return.
did we identified the data range ?
--
thanks


"Arvi Laanemets" wrote:

Hi

It looks like your sourse table will be grouped by items. When this is the
case, then let's assume your source table is on sheet Sheet1, with headers
in row 1, and that the output table will be on some another sheet, with
headers (for name and figure columns) there in row 3 and data validation
list dropdown in cell A1.

1. Define the cell A1 on output sheet sa a named range (Selection in my
example here);
2. For sheet Sheet1 define a dynamic named range (Item as an example)
Item=OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1,1);
3. Define named ranges
SelectRow1=MATCH(Selection,Item,0)
SelectCnt=COUNTIF(Item,Selection)
SelectRng=OFFSET(Sheet1!$A$1,SelectRow1,,SelectCnt ,5);
4. On output sheet, into call A4 enter the formula
=IF(ISERROR(INDEX(SelectRng,ROW()-ROW(A$3),COLUMN()+1)),"",INDEX(SelectRng,ROW()-ROW(A$3),COLUMN()+1))
, and copy it into range A4:D4;
5. Copy the range A4:D4 down for as much rows as you estimate you'll need to
display all rows for any item.

It's done! Select an item in cell A1 on output sheet, and according rows are
displayed. Don't forget - whenever you add data into source sheet, sort the
table by items before processing the report.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"liem" wrote in message
...
I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

Hi


"liem" wrote in message
...
I tried this but on step # 4 after i finished input data it has a blank
cell
with no DATA return.

It means INDEX(SelectRng,ROW()-ROW(A$3),COLUMN()+1) did return an error.
Check the range SelectRng - select some item from combo, then go
InsertNameDefine, select the named range SelectRng and then click on any
part of its source formula: the source sheet is activated with current
SelectRng activated (bordered with dashed line). When you don't see any area
with dashed border, then SelectRng=Nothing, and you have to test all
components of it one by one.

Btw. did you use data validation list in cell A1 on output sheet (i.e. you
did have values like "merchandise"/"gasoline"/"food" there) or was there
combo-box controls selection nunber (1/2/3) instead? When later, then you
have your error cause! I myself use always data validation lists on
worksheet - combo boxes are too cumbersome there.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )

Thank you for all response, but I am sorry I did not made clear on the drop
down from the question last night i asked.
The drop down i have in the worksheet is the comb box macro.
if the user selected "Merchandise" or "Food" then I like the result of
all the merchandise show up in this case from column b2:e3 .
Or if the user selected "Food" on the drop down then the result should be
b7:e7
the data on one worksheet and the comb box in other worksheet.

how can I used lookup function with Offset function in this case?
or a better way to get the result

thanks

liem


--
thanks


"liem" wrote:

I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

Ok, a combo box makes it a little bit more complicated.

Do you have the combo box linked to a cell? The linked cell will return the
*number* of the selected item. What's the location of the source for the
combo box?

--
Biff
Microsoft Excel MVP


"liem" wrote in message
...
Thank you for all response, but I am sorry I did not made clear on the
drop
down from the question last night i asked.
The drop down i have in the worksheet is the comb box macro.
if the user selected "Merchandise" or "Food" then I like the result of
all the merchandise show up in this case from column b2:e3 .
Or if the user selected "Food" on the drop down then the result should be
b7:e7
the data on one worksheet and the comb box in other worksheet.

how can I used lookup function with Offset function in this case?
or a better way to get the result

thanks

liem


--
thanks


"liem" wrote:

I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

yes, combo box have link cell b4.
--
thanks


"T. Valko" wrote:

Ok, a combo box makes it a little bit more complicated.

Do you have the combo box linked to a cell? The linked cell will return the
*number* of the selected item. What's the location of the source for the
combo box?

--
Biff
Microsoft Excel MVP


"liem" wrote in message
...
Thank you for all response, but I am sorry I did not made clear on the
drop
down from the question last night i asked.
The drop down i have in the worksheet is the comb box macro.
if the user selected "Merchandise" or "Food" then I like the result of
all the merchandise show up in this case from column b2:e3 .
Or if the user selected "Food" on the drop down then the result should be
b7:e7
the data on one worksheet and the comb box in other worksheet.

how can I used lookup function with Offset function in this case?
or a better way to get the result

thanks

liem


--
thanks


"liem" wrote:

I have a drop down menu when I selected a item (merchandise...) I want to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

Ok, so what is the source for the combo box?

You have to match the value of the linked cell to the source in order to
tell the lookup formula where to look for the data you want. For example:

Suppose you have these selections in your combo box:

merchandise
gasoline
food

If you select food the linked cell returns the value 3 because food is the
3rd item in the list. If you select merchandise the linked cells value is 1
because merchandise is the 1st item in the list. So, you have tell the
lookup formula what the number in the linked cell represents.

--
Biff
Microsoft Excel MVP


"liem" wrote in message
...
yes, combo box have link cell b4.
--
thanks


"T. Valko" wrote:

Ok, a combo box makes it a little bit more complicated.

Do you have the combo box linked to a cell? The linked cell will return
the
*number* of the selected item. What's the location of the source for the
combo box?

--
Biff
Microsoft Excel MVP


"liem" wrote in message
...
Thank you for all response, but I am sorry I did not made clear on the
drop
down from the question last night i asked.
The drop down i have in the worksheet is the comb box macro.
if the user selected "Merchandise" or "Food" then I like the result
of
all the merchandise show up in this case from column b2:e3 .
Or if the user selected "Food" on the drop down then the result should
be
b7:e7
the data on one worksheet and the comb box in other worksheet.

how can I used lookup function with Offset function in this case?
or a better way to get the result

thanks

liem


--
thanks


"liem" wrote:

I have a drop down menu when I selected a item (merchandise...) I want
to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks

The source of comb box depend on the user what they want to see.
I have merchandise, gasoline for food as the factors in the comb box.
If the user selected "merchandise" in the comb box. how can I get all three
row back at the same time ( B1 to E3) .
john robert 500 40 4 .2%
jeff gordon 800 90 4.5%
julia robel 1000 90 6.5%

or if the user selected Gasoline, i like to have the data back from ( B4: to
E6)

mak dune 50 34 3,2%
nancy thom 90 37 8.1%
John good 200 34 5.4%

or if the user selected Food the result only back on one row b7:e7

john Robert 19 11 4.4%

i hope you can show me the way to get these data back depend on the user
select in the comb box. the comb box point to row a4 and the result show up
in row a5.

--
thanks


"T. Valko" wrote:

Ok, so what is the source for the combo box?

You have to match the value of the linked cell to the source in order to
tell the lookup formula where to look for the data you want. For example:

Suppose you have these selections in your combo box:

merchandise
gasoline
food

If you select food the linked cell returns the value 3 because food is the
3rd item in the list. If you select merchandise the linked cells value is 1
because merchandise is the 1st item in the list. So, you have tell the
lookup formula what the number in the linked cell represents.

--
Biff
Microsoft Excel MVP


"liem" wrote in message
...
yes, combo box have link cell b4.
--
thanks


"T. Valko" wrote:

Ok, a combo box makes it a little bit more complicated.

Do you have the combo box linked to a cell? The linked cell will return
the
*number* of the selected item. What's the location of the source for the
combo box?

--
Biff
Microsoft Excel MVP


"liem" wrote in message
...
Thank you for all response, but I am sorry I did not made clear on the
drop
down from the question last night i asked.
The drop down i have in the worksheet is the comb box macro.
if the user selected "Merchandise" or "Food" then I like the result
of
all the merchandise show up in this case from column b2:e3 .
Or if the user selected "Food" on the drop down then the result should
be
b7:e7
the data on one worksheet and the comb box in other worksheet.

how can I used lookup function with Offset function in this case?
or a better way to get the result

thanks

liem


--
thanks


"liem" wrote:

I have a drop down menu when I selected a item (merchandise...) I want
to
return three row and four columns (B to e)
drop down menu merchandise
Gasoline
Food
A B c d e
merchandise john robert 500 40 4 .2%
merchandise jeff gordon 800 90 4.5%
merchandise julia robel 1000 90 6.5%
gasoline mak dune 50 34 3,2%
gasoline nancy thom 90 37 8.1%
gasoline john good 200 34 5.4%
food john Robert 19 11 4.4%

what is the formula when I used vlookup(merchandise ...)
--
thanks