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

#### craigwojo

##### Board Regular
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

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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

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

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.

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

OK, let's try this. It works for me. Here is a sample of my layout.
Book8
BCDEF
1HeightofTagLinesoftextpertagmyList
21.253lines0lines
31line
42lines
53lines
64lines
75lines
86lines
97lines
108lines
119lines
1210lines
1311lines
1412lines
1513lines
1614lines
1715lines
1816lines
1917lines
2018lines
2119lines
2220lines
2321lines
2422lines
2523lines
2624lines
2725lines
2826lines
2927lines
3028lines
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.

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

Is there other ways of doing this?

Thank you,
Craig

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
ABCD
10linesHeightofTagLinesofTextperTag
21lines160lines
32linesA1:A20
43lines
54lines
65lines
76lines
87lines
98lines
109lines
1110lines
1211lines
1312lines
1413lines
1514lines
1615lines
1716lines
1817lines
1918lines
2019lines
2120lines
sheet1

Replies
0
Views
656
Replies
1
Views
559
Replies
3
Views
804
Replies
5
Views
953
Replies
0
Views
492

1,196,426
Messages
6,015,176
Members
441,882
Latest member
LostinExcelHelp

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back