dependent drop down list for custom made estimate form

[ghendi]

Hello,

I joined this forum a little while back and was kindly helped by a few individuals.
I am now back looking at a different little venture.... I am trying to get a dependent drop down list in my construction estimate form/bid. The form has a lot of things I do not understand, and some I do. I am trying to get it to be easier to use for the ladies in the office who understand less about construction itself.

I added a new sheet and called in 'CSI DETAILED'. Each of those Divisions need to correspond to the correct division in sheet 1, 'ESTIMATE'.

ATTACHMENT: http://s000.tinyupload.com/?file_id=09838149016581997079

SHEET 1:

• in the B column, a drop down list exists for the estimator to choose from according to what needs to be estimated.
• the function i want to add is that in the C column.
  • function to add: a drop down list DEPENDENT on what is chosen in the drop down list in the B column.
    • example: so the desired item is chosen in B column called "01800 - FACILITY OPERATION". So the only options that would be available in C column would be those shown in sheet 4, 'CSI DETAILED', in the corresponding row belonging to "01800 - FACILITY OPERATION".
    • this function would need to be available to the correct division in sheet 1, 'ESTIMATE', corresponding to the correct B column choice.
• the developer is very hard to reach, and is non-existent when it comes to help, but he left notes on the sheets in order to understand how to add/remove and do certain things. he did this because he said some formulas can break, so he protected the workbook.

For whoever helps or gives advice or does anything (positive or negative ), thank you very very much.


Respectfully,

Amit

 

[ghendi]

bump

Any help would be greatly appreciated.

-Amit

 

[ghendi]

bump

 

[ghendi]

bump

 

[jorismoerings]

Hi Amit,

just follow The link http://www.contextures.com/xlDataVal02.html

 

[ghendi]

Hi Amit,

just follow The link http://www.contextures.com/xlDataVal02.html

Thanks, I'll try it out now!

-Amit

 

[ghendi]

I have been playing with this for a few hours.... I don't seem to be able to get the list to work since the "Define Name" function doesn't allow for the name to have numbers and spaces. From the videos in the link you had given me, she shows the Name matches the content of the source cell. So she does Countries and their Cities. So the Name for the range of cities is the name of the country she put in the Countries range. So what I am supposed to do?

Thanks

-Amit

 

[ghendi]

Okay,

I was able to name the list with the numbers in front. Now, how do I create the dependent list (which is horizontal) to that source?

On another sheet, I have the =DIV_ONE (which is the named range for the list) under column A, and then in column B, I want the dependent list to work according to what was chosen in Column A. This is where I am failing. I keep getting: "The Source currently evaluates to an error. Do you want to continue?"

Thanks.

-Amit

 

[jorismoerings]

Hi Amit,

Usualy this error occurs if, on creating the validation rule in the drop-down list in column B, the reference, in your case what's mentioned in column A, is empty. Would this be the case, just fill column A and it should work.

 

[ghendi]

Ok, I was able to get it to work, partially. It forced me to change all the data headers since it doesn't accept numbers for the define name list, and the define name list of the dependent list must match the data headers.

What I still need to get done:

• The Source List in sheet 1, Column B, has been updated, kind of. I need it to default be available to choose from the list when a new row is inserted using the "Insert Row" button installed at the top. I'm guessing that's somewhere in the VBA code?
• Sheet 1, Column C (I added that column)... I tried to add in the VBA code that the data validation list width displayed is wider, but it didn't work.
  • i copied it from the code for column B, but it didn't affect Column C.
• Sheet 1, Column C... Sometimes multiple data will be needed to be chosen for just one dependency from Column B. So for example, In Column C you choose the general topic, and then a row below that in Column C you would choose the sub-topic associated with the previous source in Column B. The thing is that sometimes i will need to choose multiple Items, one on each row, for a single source. So two or three sub-topics before i choose a different topic in column B.
  • I truly hope i maintained some level of clear thought during that explanation.

Please help. I will send you anything you require or try anything, too. I don't understand code and I'm guessing this is functionality that needs to be edited in the current code or created from scratch. Here is the current progress of my excel file: http://www.s000.tinyupload.com/index.php?file_id=09299139926479551001

Thank you very much!

-Amit