Drop down box - inputs

[RobSA]

Hi folks,


I have a drop down box copied from a spreadsheet which will read data from a page in the workbook but it wont allow me to change the range.


It doesnt seem to be the normal drop box as ti one allow me to change the text colour and border colour and the drop down arrow only appears when you hover over the text.


I would value any help that I may get to change the range of the data required ?


Regards

Rob

 

[Luke M]

Sounds like an odd one. Is it one of these?


1 Data validation drop down

2 Combo box from the Forms toolbar

3 Combo box from the ActiveX toolbar


When you say "hover", do you mean that you select the cell/box, or is it litterally just appearing when you move the mouse over the object?

 

[RobSA]

Hi Luke,


I need to select it sorry.


When I select it a little marker appears on the right hand side with a down arrow and I can then select the project listed therein.


I cannot change the range to allow me to add additional projects.


I do not know which of the three it may be that you have listed above as I do not have enough knowledge to reply.


Regards

Rob

 

[Hui]

Rob

Select the cell

Goto the Data, Validation tab

Select Data Validation

Is there a list, formula or Range shown in the dialog that pops up?

Edit as appropriate or tell us what it say

 

[RobSA]

Hi Hui,


Thank you for the reply.


I select as requested and the first pop up appears with an option of three Y/N/Cancel


The text that appears above:


The selction contains some cells without validation settings. Do you want to extend data validation to these cells?


I hope this helps

 

[Luke M]

Sounds like you may have selected more than one cell. Try again, but this time select only a single cell. I'm guessing that the Data Validation dropdown is what is going on. Within Data Validation, you can either reference another range to provide the inputs or you can hard code what you want the inputs to be.

 

[RobSA]

Hi Luke,


Thanks for the reply.


I am not offered that opportunity to select on cell.


When I select the shaded area which spans seven columns on one row the above result applies.

 

[Luke M]

Hmm. Sounds like it's a merged cell. When the pop-up appears, click "no", and then see what's in the dialogue box.

 

[RobSA]

Hi Luke,


It provides a different reply, with three tabs "Settings", "Error Alert" and "Input message"


I have tried to play around a bit here as well so as to change the range -to no avail.


Regards

Rob

 

[Hui]

RobSA

Can you post a sample file for us?

 

[RobSA]

Hi Hui,


I have placed the file in this link


https://www.dropbox.com/sh/fxha3cu3y2llvm9/xkE8eRGxz1?m


I hope this works. The issue we are discussing is found on the first worksheet DB01 in cell N2.


I look forward to your replies and inputs to the improvement.


Regards

Rob

 

[Hui]

RobSA


Yes, The merged cells were getting in the way


Unmerge N2

Select N2

Goto the Data, Validation tab

Select Data Validation

Note that the Data Validation is using a List

from the Source: Period_List


If you leave the Data Validation Dialog

goto the Name Manager which is on the Formulas Tab, Name Manager


Click on the Period_List and note that the Refers To: shows: =INPUTS!$B$3:$B$15

Close the Name Manager


Change to the Inputs sheet

See the list in cell B3:B15


You will see that the list is already extended to the end of 2012 in cell B27


So go back to the Formula Tab, Name Manager

Select the Period_List name

Edit the range in the Refers To: window to =INPUTS!$B$3:$B$27

Save


Enjoy


You may want to re-merge N2 with the cells beside it again

 

[Debraj]

Hi RobSA,


* Select Cell N2, then click >

Data Validation .. If it ask for

"The selction contains some cells without validation settings. Do you want to extend data validation to these cells?" then click Yes..

You will found that

Source = Period_List

If you click it will take you to the Period_List Section.


* comeback to Excel, by clicking Cancel..

Go to Formula > Name Manager..

Search For Period_List and Click EDIT..

Now Click on the Refer To Section

Currently it was =INPUTS!$B$3:$B$15

Sheet Name "Input" Range Area B3 to B15..

Change it to B3 to B27..(or some More if you want to add January 13, February 13 also..)


So

1> Data validation is on Period_List.

2> Period_List is on Name Manager

3> Name Manager allow you to extend List..

as you can see. some of the cells in the Input Sheet B Column are not added in the Period_List section, that why EXCEL ask you below..

The selction contains some cells without validation settings. Do you want to extend data validation to these cells?


Any issue please let us know..


Regards,

Hui's Fan..

 

[Debraj]

Hi Ian,

oooppssss..