Drop down menu

[Stclements1]

I have created a drop down menu consisting of 16 products. In column H of my spreadsheet I list the items that I am trading. For example on H3 I want to have the drop down menu and click on , for example, silver and the text "Silver" is in cell H3 and once a product is selected the drop down list moves to cell H4 where I might select "Copper" and the drop down menu moves down to cell H5 where I may select "Silver" again and the drop down menu moves down to H6 and so on. My question is, is this possible because I have created a drop down but after that I am stuck.

 

[sykes]

You would get more answers if you gave a little more accurate detail in your question...
How have you created a "Drop-down menu?"
How have you populated it with your 16 products?
I'm assuming it's a data validation scenario - with the validation set to "List." Can you confirm this?
Just how many of your H column rows do you want to have this availability? 20? 50? 1,000? 10,000?
Where is your dropdown at the moment?

Just about anything's possible with Excel, and the Folk on this site will give up their spare time, and go to the ends of the earth to help anyone, but it's very frustrating to have to dig for enough information to give help, and even more so, to find that after many posts, the scenario / requirements were actually very different to those first mooted.

 

[Stclements1]

Its because I am a novice that I use this site, I have made a list by filling 16 cells in a row with 16 products, I then use ctrl T to make a drop down table, thats what I have I need the H column rows to number 100, I have no idea what a date validation scenario is. i am simply trying to select a product from the list I created by using ctrl T and then Ias I select a product from that list I want the selected product to remain in the cell where the list was and the list to move down to the next cell so that I can use the list again when I am ready to populate that cell. That is all the information I can give. If this is not enoughthen I thank you for your time and efforts and I shall have to look elsewhere.

 

[sykes]

Data validation should work well for this.

You can define your "list" in many ways, but the easiest might be to just type your metals into 16 cells somewhere on your spreadsheet that won't be in your way; perhaps P1 - P16.
Select the "H" cells in which you want the list to appear (H2:H100), perhaps?
At the top of Excel (in the "Ribbon") find the "Data Tools" section, and within that, the "Data Validation" button/ dropdown.
Selecting this, should result in the data validation userform appearing.
In the "Allow" window, select "List"
In the "Source" window click the up arrow at the far right. This will minimise the validation userform - allowing your to go and select your "List" of metals in column P.
Once selected, the P range will appear back in the data validation userform; use the far-right down arrow to expand the form again.
Click "OK."

Now, any of your selected H col cells should have an arrow appear whenever you select them, and if clicked, the arrow extends into your list - from which you can pick your metal.
Once you've done this a few times, you can actually just type-in the first couple of letters of a metal, and it'll appear for you. Using the Return (or Enter) key at this point, will not only put the metal into the cell, but move the cursor down to the next cell - for you to do the same.
It should be very quick to use.

 

[sykes]

...as I said, there are other ways of defining your list of (in this case) metals:

1. Define a "Named range"
2. Another good way, is, having selected "Data validation" for your "H" range, in the "Source" box, simply type your metals into the box - separated by commas.

Whichever way you do it, you can always change it (add/delete entries) afterwards - just remember to extend the data validation range, should you wish it to work on more cells on your spreadsheet(s).

Let us know how you get on, as the feedback is useful for other folk to see whether this might work for them / solve their similar requirement.

 

[Stclements1]

That first one works perfectly, many thanks for your help, I will try to be as descriptive as possible in future questions. Thank you so much for your time and effort.

 

[sykes]

Pleasure. Glad it was what you were after, too.

 

[Stclements1]

Not sure whether I'm allowed to follow on with another query although it is related to the same spreadsheet, each of the products in the drop down list has what in trading is termed as a margin rate, for example if I trade $1000 of (I'm in the UK but I don't know where the GBP sign is so used the $ instead) Gold the margin is as the spreadsheet below shows. In my spreadsheet I inpit the product in column H and the margin in column 0, would it be possible that when I select the product the margin value automaticaaly appears in column 0? Sorry if I've overstepped.

NIKKEI	0,50%​
EURO 50	0,50%​
FTSE	0,20%​
DOW	0,20%​
S&P	0,20%​
GBP/EUR	0,20%​
GBP/YEN	0,20%​
GBP/USD	0,20%​
USD/CHF	0,25%​
GBP/ZAR	4,00%​
BRENT	0,75%​
COPPER	1,00%​
SILVER	1,00%​
WTI	0,75%​
GOLD	0,50%​
PLATINUM	2,00%​

 

[sykes]

Once again, lots of ways of doing this - and someone else might step-in with a better one. Until then...

You could make a table of data - extending your P column (or wherever you have your current markets stored).
I've done mine, thus:
Data Table:

Working cells:
Below, Just for the sake of illustration, I've put the margin rate in col I, but it can go wherever you like, of course.

Mr Excel.xlsm
	H	I
1	Market	Margin rate
2
3	Copper	1.00
4	DOW	0.20
5	Euro 50	0.50
6	Brent	0.75
7	Euro 50	0.50
Sheet1

Cell Formulas
Range	Formula
I3:I7	I3	=INDEX($Q$2:$Q$8,MATCH(H3,$P$2:$P$8,0),)

Cells with Data Validation
Cell	Allow	Criteria
H2:H7	List	=$P$2:$P$8

Above, you can see the formula in I3 - to pick up the margin rate from your table.
Now, selecting a different market in col H, automatically causes the Margin rate to change, too.

I've used absolute reference, so you should be able to copy the formula into your work, change the references to suite, then fill down - which should save time & effort!
Depending upon how you have your "Margin" col formatted, you may need to mess around with formatting - to get the % character to display. Alternatively, show % in the header ("Margin Rate %") then it'll be easier for you, as you can do away with needing to deal with formatting.

As I said, there will no doubt be other ways of doing this, so this is just one.
Another solution would be to use the "Lookup" formulae, but I tend to steer clear of these, as some need the lookup table to be sorted alphabetically etc, and this can cause problems in the future.

 

[Stclements1]

Sorry for the delay, this works perfectly, many thanks once again.