Select from List

M

Mysore

Hi Guys,

I have created a list and I want to automatically choose from the list which
pops up when I type the word.

Is there some funtion which I need to amend to the list.

Appreciate your feedback.
Thanks
 
M

Mysore

Hi Fred ,

Below is the list I have created in sheet two. Through Data Validation in
Sheet one , I have used the list option to link with the sheet where I have
got the below list.
I want this to automatically pop up in sheet 1 when I type the word.
Hope this clears

TRANS TASMAN
BROC FRAG
BROC PAPER
BROC PRINT
BROC MISC
LETS TALK
NET OFFER
PURCHASE ORDER
PREPAID WELLBEING
DM INCENTIVES
MISC EXP
COURIER
INTERCO CHGS - FINANCE
INTERCO CHGS - MKTG
INTERCO CHGS - ORDER FLOW
 
M

Max

Here's some thoughts ..

Assuming the reference data set, viz:
TRANS TASMAN
BROC FRAG
BROC PAPER
Click to expand...
etc

are in a named col range, eg:
MyRange: =Sheet2!$A$1:$A$15
(above created via Insert > Name > Define)

Then in any sheet, say in Sheet1,
let's assume A1 will be where we input the name: MyRange

We could place in say, A2:
=IF($A$1="","",IF(ROW(A1)>COUNTA(INDIRECT($A$1)),"",INDEX(INDIRECT($A$1),ROW(A1))))
then copy A2 down as far as required to cover the max expected range size.
In A2 down will be extracted the contents of the col range whose name is
input in A1. Adapt to suit where you want the contents to appear.
 
M

Max

We could place in say, A2:
=IF($A$1="","",IF(ROW(A1)>COUNTA(INDIRECT($A$1)),"",INDEX(INDIRECT($A$1),ROW(A1))))
Click to expand...

Note: If/when adapting the above to suit your layout .. always use "ROW(A1)"
in the starting cell's formula (eg placed in A2). Do not change this. It has
nothing to do with the fact that the input cell for the named range is in A1.
ROW(A1) is simply used as an incrementer in the formula to return the numeric
series: 1,2,3 ... when we copy the formula down from the starting cell (I use
"ROW(A1)" out of convention).
 
Top