Data Entry Control in Excel? 1

[lameid]

So Access is the correct solution for this but I need to do it in Excel and I have no idea where to begin...

Really simple concept I have a multi-column list that I want people to be able to pick from to populate row(s) in antother sheet (along with other details).

I played around with a combobox for a couple minutes but quickly realized this was only good for one cell per combobox at a time... My original thought was to populate my unique column this way and use vlookup for the other columns.

Excel 2007. I'm just totally clueless about what Excel concepts to even read about at this point.

 

[SkipVought]

Hi,

How about using Data > Validation -- LIST? Works similar to a combobox.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Cool and thanks.... but I don't think it is quite effective for this purpose...

My list is over 570 elements and growing and literally a list of integer key values...

So I don't like that you cannot type in the value like a combobox to speed selection. Also it would be really helpful to display three columns in the drop down.

Any other thoughts? I know I have the right guy.

 

[SkipVought]

3 columns? Not in a Data Validation LIST.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Thanks again...
I hate having the wrong tool for the job.

 

[xlbo]

How about you give us an example of what you need to achieve and we'll suggest the best way to do it?!?

In other words, can you expand on "Really simple concept I have a multi-column list that I want people to be able to pick from to populate row(s) in antother sheet (along with other details)"

What would go in the multi ciolumn list?
How does it relate to the "Other Details"
Can you give a worked example?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[jonsi]

Have you considered the INDIRECT function?

Assume a 3 column list in columns C,D & E running from Row2 thru 3001 (so 9,000 bits of info) on Sheet1 (Sheet1!C2:E3001)

On Sheet2 where your unique column is (let's say it's in Column B) the user keys in the Cell address for the return he wants into Cell A2, say D600. Have 'Sheet1'! written into Sheet2 Cell A1 and have =INDIRECT($A$1&$A2)as the formula in B2. Copy this formula from B2 down column B as far as you wish.

On the next row (Row 3) the user would input his next selection, say E2710, into A3. Cell B3 would read =INDIRECT($A$1&$A3) but would return the contents of Sheet1 Cell E2710 (Sheet1!E2710)

It's not as sweet as a Drop Down (in that the User has to refer to the source sheet to get the Cell Reference from the Name Box) but it has the same result in that you can populate your unique column by reference AND the original can be resized so making it easier to read than a Drop Down.

(you could always Lock Sheet1 to prevent unauthorised users making changes)