Hi everyone, first post here.

I have uploaded my excel file. it is 26kb
http://members.optusnet.com.au/adrianloh01/Boots.xls

I have a test tomz at uni, and i need to be able to bring data from
another sheet (sheet 1) from a droplist box on another sheet (sheet 3).
These are just sales figures for a motorbike boots (my assignment). I
have made the drop downlist that has all the "sales agents" in it, but
i need to know how to make it so when you click one "agent" the data
comes from sheet 1 and brings up the relevant sales information.

I have started putting the headings and needed columns on sheet 3.

If anyone could help me here, i would really appreciate it.

thanks,
Ady


--
bnr32
------------------------------------------------------------------------
bnr32's Profile: http://www.excelforum.com/member.php...o&userid=26739
View this thread: http://www.excelforum.com/showthread...hreadid=399966

Hi

To get data validation list to accept list from another (with unique
entries) sheet as source, you have to define it as named range, and to refer
to this named range.

To get the linked (from same row in source table) to selected key value
information to be displayed, you can use LOOKUP function, like
=VLOOKUP(LookupValue, LookupTable, ColumnNumber,0)
where LookupValue is the value selected from dropdown, LookupTable is range
reference to table with all unique lookup values in leftmost column, or a
reference to identical named range, and ColumnNumber is the relative number
of column with info to return in LookupTable (the column with LookupValue's
is 1st, etc.)

When you want to get info from several rows, based on dropdown-selected
value in some cell, it'll get more complicated. I myself use in such cases a
hidden column as leftmost in source table, where rows with same key value as
in dropdown are numbered (1, 2, 3, ... etc.). Something like
=IF(B2=SelectedValue,COUNTIF(B$2:B2,SelectedValue, "")
Now you can get all roes from source table, using VLOOKUP like
=IF(ISERROR(VLOOKUP(Number,SourceTable2,ColumnNumb er,0)),"",VLOOKUP(Number,SourceTable2,ColumnNumber ,0))
where SourceTable2 is the range woth hidden column as leftmost one.


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


"bnr32" wrote in message
...

Hi everyone, first post here.

I have uploaded my excel file. it is 26kb
http://members.optusnet.com.au/adrianloh01/Boots.xls

I have a test tomz at uni, and i need to be able to bring data from
another sheet (sheet 1) from a droplist box on another sheet (sheet 3).
These are just sales figures for a motorbike boots (my assignment). I
have made the drop downlist that has all the "sales agents" in it, but
i need to know how to make it so when you click one "agent" the data
comes from sheet 1 and brings up the relevant sales information.

I have started putting the headings and needed columns on sheet 3.

If anyone could help me here, i would really appreciate it.

thanks,
Ady


--
bnr32
------------------------------------------------------------------------
bnr32's Profile:
http://www.excelforum.com/member.php...o&userid=26739
View this thread: http://www.excelforum.com/showthread...hreadid=399966

Wow, thanks Arvi,
really appreciate the help.
I didn't know you had to actually highlight the droplist in the
VLOOKUP() formula, i thought you would just have to set the VLOOKUP()
formula on the actual droplist.

I used the first method you said, and it worked great, now i can pass
my test :D

thanks again.

ady


--
bnr32
------------------------------------------------------------------------
bnr32's Profile: http://www.excelforum.com/member.php...o&userid=26739
View this thread: http://www.excelforum.com/showthread...hreadid=399966