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

 

[L. Howard]

Hi Amit,

Your link goes to an error page not available...?

Howard

 

[ghendi]

Hi Amit,

Your link goes to an error page not available...?

Howard

Thanks Howard. Must have pasted it in wrong. Here's the correct link: http://s000.tinyupload.com/index.php?file_id=09299139926479551001

 

[L. Howard]

•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?

One bullet item at a time:

Sheet 1 is CSI DETAILED. I see no Source List name or header there for column B.

The first tab in the workbook is Estimate and I see no Source List for column B there either. However, it is a very busy sheet (Estimate) so I may be overlooking it.
Once Source List has been located, what is the "Insert Row" - default etc. problem.
Can you be more specific, exactly what is not happening or what should happen?

Howard

 

[L. Howard]

•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.

On sheet 2 Estimate, in the VB Editor, make this change to the Worksheet_SelectionChange macro and delete the one that is just below it as shown here in bold.

Seems to work on column C as it does on column B in my tests.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const ValidWidth = 5 'Change here to change the width of the Data Validation list
    If Target.Column = 2 Then MakeValidationWidthWide Target, ValidWidth
    If Target.Column = 3 Then MakeValidationWidthWide Target, ValidWidth
End Sub

[B]'Private Sub Worksheet_SelectionChangeSub(ByVal Target As Range)
'    Const ValidWidth1 = 5 'Change here to change the width of the Data Validation list
'    If Target.Column = 3 Then MakeValidationWidthWide Target, ValidWidth1
'End Sub
[/B]

Howard

 

[L. Howard]

Use this, where you can change column B or C to individual widths to suit.

Delete the other selection change macro.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Const ValidWidthB = 5 'Change width of the Data Validation column B
    Const ValidWidthC = 5 'Change width of the Data Validation column C
    
    If Target.Column = 2 Then MakeValidationWidthWide Target, ValidWidthB
    If Target.Column = 3 Then MakeValidationWidthWide Target, ValidWidthC
End Sub

Howard

 

[ghendi]

•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?

One bullet item at a time:

Sheet 1 is CSI DETAILED. I see no Source List name or header there for column B.

The first tab in the workbook is Estimate and I see no Source List for column B there either. However, it is a very busy sheet (Estimate) so I may be overlooking it.
Once Source List has been located, what is the "Insert Row" - default etc. problem.
Can you be more specific, exactly what is not happening or what should happen?

Howard

Hello Howard,

Thank you for your help. I was out of town and did not have access to try and reply to you until now.

In column B, when you select a choice from the drop-down list, then the sub-list in column C should be activated and dependent on the choice in column B. The problem I'm having is that the text/data in column B is long and extends into column C, so the list in column C has to be one row below. Also, sometimes I may need to select Plumbing Fixtures in column B, and then specify the multiple different work that needs to be done in column C. That means I may need to select more than one item in column C, and each one will be on a different row. Can it be coded so that column C depends on the previous column B choice until a new column B is chosen?

The problem with the "Insert Row" button is that it adds a row that isn't connected to the "validation data" list that I had created in 'CSI DETAILED'.

Your change to Worksheet_SelectionChange worked perfectly, thank you.

Thanks for your help Howard.

-Amit

 

[L. Howard]

Hi Amit,

With your Group/CSI Code/Description drop down columns, I notice you have only three examples in somewhat of a completed state. So I suppose you are in the early stages of setting up this entire General Estimate sheet/workbook.

I'm thinking you may want to rethink your layout, if that is an option.

Having the dependent drop down offset a row from the primary drop down is a bit awkward, although I see you reason, what with such long entries. If possible it would be better to have columns wide enough to accommodate the entries on a single row. That would require sheet modifications but is probably workable, if you have the latitude to adjust the sheet and still get the stuff done you want.

The use of merged cells can be a problem also, but "Center Across Selection" works well and is less troublesome.

If I understand the row deletion/additions caper, it is to accommodate the drop down lists as they grow or diminish. If that is true then again, it looks like you should rearrange the data on sheet CSI Detailed to be in columns instead of rows for the drop down items. Then you can use a common dynamic formula in the drop down source window that adjusts to the length of the list of items automatically. No need to manually adjust the range in the source window as the formula will do just that by itself.

Like these:

=OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A$1:$A$200),1)
=OFFSET($A$2,0,0,COUNTA($A$2:$A$200),1)

Here you would just add items to the list referred to in column A and the formulas would pick the items up all the way down to A200.
That gets rid of two buttons on the Estimate sheet and any need to add/delete rows for the drop downs.


This site for selecting multiple items from a drop down. Might be a bit tricky, especially with the set up of your sheet.

http://blog.contextures.com/archive...ltiple-items-from-excel-data-validation-list/

I don't feel I can breath life into the sheet as you are proceeding, but would give it a go if you want to revise it.

Howard

 

[ghendi]

Hey Howard,

Thanks for getting back to me. I would be happy to revise it. It is built to be printed on a landscape orientated Legal size paper. How would you recommend to proceed while keeping the relevant data?

Here is an example of what I was referring to:

Thanks!!

-Amit

 

[L. Howard]

Hi Amit,

I'll post a link to tiny example of how a few or many items can be listed by way of a drop down.

Perhaps we can transform the example into your larger data base or some thing like it.

Might be a way to start.

Howard

 

[L. Howard]

Have a look at this little sample, substitute your items and sub items for the dummy data shown.

Might take some imagination.

https://www.dropbox.com/s/rsb9vvl074jsj0m/Drop down Changing List via Macro.xlsm?dl=0

Howard