Drop menus

Hi sludeking,

welcome to the forum.

for your dropdown list in the Kode column, you can use a simple data validation with a list that you specify on a different sheet. See here how data validation with a list can be set up http://www.contextures.com/xlDataVal01.html.

Then, next to that list that you use for data validation, you store the values that you want to return with a lookup. Give the lookup table a range name, for example DataTable. Then in your column next to Kode, you can then use a formula like

=vlookup(D3,DataTable,2,False)

To fine tune the formula and show you in more detail how this could work, please upload a file with some sample data. You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

cheers

What is not working? Can you upload a sample file?

Hi, you can use Vlookup for that. First you need to create a table where excel can find the values you want to fill in automatically. You can do that on your Lists sheet.

Please Login or Register  to view this content.

Select the whole table and then assign it a range name, for example LookupTable. Then in your data entry sheet, use this formula in B5, copy down and across

=VLOOKUP($A5,LookupTable,COLUMN(),FALSE)

The Column() nested in the VLookup will return the number of the current column, i.e. B=2, C=3 etc. If you position your lookup table in the same columns as the data entry table (just on a different sheet), this is probably the easiest way to define which column of the lookup table to return.

hth

Okie. That helped and sense teylyn. Thanks.

When I enter the Vlookup formula and drag it down to autofill the other cells in the spreadsheet:

=VLOOKUP(A26;LookupTable!A2:D22;2;FALSE)

LookupTable!A2:D22 increases each time. A2:D22->A3:D23->A4:D24 etc. Can I prevent it from doing so??

In the formatting dialog on the protection tab, you can set individual cells to "Locked". This is the default, so you need to "unlock" cells that you want people to edit.

Then you need to protect the worksheet. You can do that with or without a password. I'm not too sure about the Excel 2008 (Mac) interface, but if it is anywhere similar to 2007 (PC) then it's on the Review ribbon - Protect sheet, or on the Home ribbon - Format - Protect sheet.

There are several fine tuning options that you can set, i.e. if you want to allow the user to even select protected cells or not (if they can select protected cells, they can see the underlying formula in the formula bar, but they cannot change the formula. If they can not select the cells, they have no way of figuring out the formula unless they unprotect the sheet).

If you choose to set a password for the worksheet, be aware that sheet level password security is not fail safe and can easily be circumvented. It's more to protect the sheet from accidental changes rather than a real security tool.

hth

Sludeking, you changed post #9 after I replied to it. Please do not change posts that have been replied to. It really upsets the flow of the conversation. You may be surprised at how quickly people answer on here sometimes, so just use the refresh button of your browser before you edit a post to make sure there have not been any replies.

The response in my previous post was with regards to your original content of post #9, which was

Another question. Now that the various cells have the Vlookup formulas in them, can they be locked so they are unable to be edited, deleted, formatted, etc?

To answer what's currently in your post #9:

change =VLOOKUP(A26;LookupTable!A2:D22;2;FALSE) to

=VLOOKUP(A26;LookupTable!$A$2:$D$22;2;FALSE)

The dollar signs make the cell references absolute, that means they will not be adjusted when copied down or across.

Nope. AFAIK, a drop-down (aka data validation list) is one list. You choose one of the options presented. Nothing else.

But you can have two columns next to each other, where the first column is used to pick a year, the next column is used to pick a month, and you can then evaluate/concatenate the choices in yet another column.

Care to post a sample of what you'd like to achieve?