LISTS

[sedonovan]

I want to create a list of codes of initials as a drop down list - which I
know how to do,
but I want them to see the full name but only the initials to be inputted
into the table

eg they see
JB Joe Bloggs
SD Suzie Donovan
JD Jack Daniels

but only the SD part is inserted into the spreadsheet,

is this possible??

Many thanks in advance

 

[Max]

Here's one play to achieve this
using a combo box from the control toolbox toolbar ..

A sample construct is available at:
http://www.savefile.com/files/5512788
Combo box from control toolbox toolbar_example.xls

Assume source data is in Sheet1!A1:B3
col A = names , col B = initials, viz.:

Joe Bloggs JB
Suzie Donovan SD
Jack Daniels JD

(switch it the other way around)

In say, Sheet2,

Draw a combo box from the control toolbox toolbar
Right-click on the combo box > Properties

Set the properties of the combo box to:
ListFillRange: Sheet1!A1:B3
LinkedCell: B2
BoundColumn: 2
ColumnCount: 1

Click "Exit Design Mode" on the Control Toolbox toolbar, and test it out.
The combo box droplist will display only the names, & selecting the name will
place the corresponding initial into the link cell: B2

And if we set ColumnCount to 2, then the combo box *droplist* will display
both names & initials (this doesn't affect the usage, only the initial will
be placed into B2)

 

[sedonovan]

Thanks, that WAS helpful, I've done that now, but what about information I
need to store in B3...Bx? Is that possible?

 

[Max]

.. but what about information I need to store in B3...Bx?

Ah, then it's easier to use data validation ..

Assuming the same setup, viz.: names & initials in Sheet1's A1:B3

Select A1:A3, and name the range as: Name
(via: Insert > Name > Define)

Then in Sheet2,

Select the range say, A2:A20
Click Data > Validation, make the settings:
Allow: List
Source: Name
Click OK

Then put in B2, and copy down to B20:
=IF(A2="","",VLOOKUP(A2,Sheet1!A:B,2,0))

Col B will extract the initials for the names selected in col A

Adapt to suit ..

 

[sedonovan]

That's wonderful! Thanks

Ah, then it's easier to use data validation ..

Assuming the same setup, viz.: names & initials in Sheet1's A1:B3

Select A1:A3, and name the range as: Name
(via: Insert > Name > Define)

Then in Sheet2,

Select the range say, A2:A20
Click Data > Validation, make the settings:
Allow: List
Source: Name
Click OK

Then put in B2, and copy down to B20:
=IF(A2="","",VLOOKUP(A2,Sheet1!A:B,2,0))

Col B will extract the initials for the names selected in col A

Adapt to suit ..

 

[Max]

That's wonderful! Thanks

You're welcome !