Excel Drop down box

[sterne.law@gmail.com]

I want to use the dropdown box in my excel, I cant for the life of me remember how.
I want the dropdown to show the populated list so that the exact word can be chosen.

if its a plug in any links to same would be appreciated.

[adrianh]

http://www.techrepublic.com/blog/mic...an-excel-cell/

[sterne.law@gmail.com]

:

Man have I been meaning to incorporate this feature for ages, no idea what took me so long to do it.

[irneb]

Yep, Excel 2007's work like that. But I prefer 2010+: It allows the drop-down's source list to much more easily be read from a table as well (instead of just a fixed address). Works wonderfully for lookup tables!

[sterne.law@gmail.com]

I'm finding pivot tables effective for data integrity management.

[adrianh]

This is the single most useful bit of code I have ever come across for Excel. The code allows you to write modules that are fired during cell entry. So, you are able to fire off lots of different events due to one selection being made in your dropdown list.

This is my scenario:
I do laser cutting and when I do costing I need to be able to select one material from a list of 10. The list of 10 materials may have different thicknesses.
So
Plywood
1mm
2mm
3mm

UHI
1.2mm
2.5mm
3mm

Now, each material / thickness has a related board size and price which gives me a calculated cost / cm sq.

When I select a particular material I have to restrict the selection of thickness to only those specified for the material type and once I select the material thickness I have to populate the material length, width, price and calculated price / cm sq fields.

I couldn't figure out how to do this because Vlookup etc is not clever enough to restrict the secondary selections.

This code allows you to do anything you like once data has been entered into a cell.

Run the example and you'll quickly figure out how useful the code could be.


XL: How to Run a Macro When Certain Cells Change

http://support.microsoft.com/kb/142154

[sterne.law@gmail.com]

Hello Adrian,

I may have a simplier way to do the same

D E F
part type supplier

plywood 2 45

WOOD THICK COST
plywood 1 35
plywood 2 45
plywood 3 55
UHI 1.2 65
UHI 2.5 75
UHI 3 85


The table is set
Two items in bold are the selection criteria
Coloured number Is the value returned by the following formulae - =INDEX(D7:F12,MATCH(D4&E4,D7:D12&E7:E12,0),3)

[sterne.law@gmail.com]

The smiley face is actually a colon :

so it reads d7:d12

[Dave A]

The smiley face is actually a colon :

I took the liberty of wrapping the problem section with noparse BB tags for you. The unintended smiley is now history