Toughy from Newbie. "It's tough for me"

[craigwojo]

Cell B2 is an input cell. Titled as "Height of Tag"
Cell D2 is a validation (list) cell. Titled as "Lines of Text per Tag"

What I need is -

Cell D2 need to list possible data from B2 input.
-For every .25 in cell B2, cell D2 needs to add another listing within it's validation(?) pull down menu.

ex. If I input .75 in cell B2, then cell D2 should only have "0,1,2,3 lines" available in the pull down menu.

ex 2. If I input .25 in cell B2, then cell D2 should only have "0,1 lines" available in the pull down menu.

ex 3. If I input 4.00 in cell B2, then cell D2 should only have "0,1,2,3 to 16 lines" available in the pull down menu.

Note: Each available entry in D2 (pull-down menu) will have the word "lines" after the number. example - 0 lines, 1 line, 2 lines, etc.

The maximum in cell B2 is 16.00.
The maximum I need in cell D2 is 20 lines.

Any assistance would be greatly appreciated.

Thank you,
Craig

 

[shades]

Check out this thread and the original:

Aladin's Dependent List Explanation

 

[frenkc1]

If you use the validation from list, you can use someting like IF [inputcell] = [criteria] THEN [show line] ELSE [don't show line]

in each line of the list you want to validate from, the formula should be dependant of the inputcell. If your criteria is matched, the formula wil give the disired result

Hope it'll help out

 

[craigwojo]

Thank you for the responses, but (sorry) I'm still lost on the way to do it. I'll look it over again and try to figure it out. Remember, still a Newbie with Excel.

Frenkc1, I dont understand what you meant by "THEN [show line] ELSE [don't show line] "

I need the pull down menu to show all of the selections that are available for the input from B2 (input cell). Is this what you meant?

Thank you,
Craig

 

[Seti]

Here is one solution using dynamic named ranges. Say you create a validation list in G2:G30 with values running from 0 to 28 (adjust to suit your maximums). You now create a dynamic named range: Insert|Name|Define and in the name box type "myList" (w/o quotes) and in the Refers to box type: =OFFSET($G$2,0,0,1+4*$B$2,1). Now in your data validation for D2, choose list and enter myList. Now change B2 and the options for D2 change as well.

 

[craigwojo]

Seti and others,

Thank you, but no luck here. Tried what you gave to my best ability but, no luck.

Here is what I typed in the first message.

Cell B2 is an input cell. Titled as "Height of Tag"
Cell D2 is a validation (list) cell. Titled as "Lines of Text per Tag"

What I need is -

Cell D2 needs to list possible data from B2 input.
-For every .25 in cell B2, cell D2 needs to add another listing within it's validation(?) pull down menu.

This is what I need it to do -

example 1.
If I input .75 in cell B2, then cell D2 should only have "0,1,2,3 lines" available in the pull down menu.

example 2.
If I input .25 in cell B2, then cell D2 should only have "0,1 lines" available in the pull down menu.

example 3.
If I input 4.00 in cell B2, then cell D2 should only have "0,1,2,3 to 16 lines" available in the pull down menu.

Note: Each available entry in D2 (pull-down menu) will have the word "lines" after the number. example - 0 lines, 1 line, 2 lines, etc.

The maximum in cell B2 is 16.00.
The maximum I need in cell D2 is 20 lines.

I don't know if I can explain it any other way. I appreciate everyone's help.

Thank you,
Craig

 

[Seti]

OK, let's try this. It works for me. Here is a sample of my layout.

Book8
	B	C	D	E	F
1	HeightofTag		Linesoftextpertag		myList
2	1.25		3lines		0lines
3					1line
4					2lines
5					3lines
6					4lines
7					5lines
8					6lines
9					7lines
10					8lines
11					9lines
12					10lines
13					11lines
14					12lines
15					13lines
16					14lines
17					15lines
18					16lines
19					17lines
20					18lines
21					19lines
22					20lines
23					21lines
24					22lines
25					23lines
26					24lines
27					25lines
28					26lines
29					27lines
30					28lines
Sheet1

I defined a dynamic named range (myList, the name of the range) refers to
=OFFSET(Sheet1!$F$2,0,0,1+4*Sheet1!$B$2,1), obviously change Sheet1 to the name of your sheet. If you need more info on this step post back.

Then I set data validation - list - myList for cell D2. Change B2 and I think you get what you want.

 

[craigwojo]

Seti

Thank you for your patience.

In column D (where you have "3 lines") is that cell a drop down menu for picking the right amount. Which in your case would be (for 1.25) a drop down menu which gives a option to pick "0 lines, 1 line, 2 lines, 3 lines, 4 lines, 5 lines.
As I stated before, only 1 line per .25 is for this case.

Thank you,
Craig

 

[craigwojo]

Is there other ways of doing this?

Thank you,
Craig

 

[erik.van.geit]

Craig,

Try out this solution:
1. fill in somewhere (hidden) a list such as here in A1:A20
2. put in a cell a formula like in B3 (adapt to the real range of the list)
3. validate cell D2 ==> LIST and in the sourcebox = data_lines
4. goto menu Insert / Name
type data_lines under "names in the workbook" and in the reference = INDIRECT(Sheet1!$B$3)
Names are referenced by formulas (most of the people think they are just referencing ranges) so this formula makes the range "data_lines" dynamic!

Is this completely explained?
For me it works good!
kind regards,
Erik

PS: Craig, thanks for PM, it was really something which learned me a lot, since I (almost?) never used dynamic ranges

Map1.xls
	A	B	C	D
1	0lines	HeightofTag		LinesofTextperTag
2	1lines	16		0lines
3	2lines	A1:A20
4	3lines
5	4lines
6	5lines
7	6lines
8	7lines
9	8lines
10	9lines
11	10lines
12	11lines
13	12lines
14	13lines
15	14lines
16	15lines
17	16lines
18	17lines
19	18lines
20	19lines
21	20lines
sheet1