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]

Hello,

So here is the new organized list for the data: TinyUpload.com - best file hosting solution, with no limits, totaly free

What's next? What do you think?

Thank you

-Amit

 

[L. Howard]

Hi Amit,

Here is a early shot the drop downs.

Not sure, but seems like it will need a third drop down for the sub items selected in the second drop down.

I was not clear to me if all the items in the second drop down choices have or require a further selection.

I sort of thought there may be something like:

DataVal - 1 = (Division_n) = PLUMBING
DataVal - 2 = Sinks
DataVal - 3 = Copper, Ceramic, Stainless, Cast Iron

Or the like...?

https://www.dropbox.com/s/tsk9p1yywhsnq3g/CSI_Data_Validation_Source Basic Data DBx V1.xlsm?dl=0

Howard

 

[ghendi]

Hi Amit,

Here is a early shot the drop downs.

Not sure, but seems like it will need a third drop down for the sub items selected in the second drop down.

I was not clear to me if all the items in the second drop down choices have or require a further selection.

I sort of thought there may be something like:

DataVal - 1 = (Division_n) = PLUMBING
DataVal - 2 = Sinks
DataVal - 3 = Copper, Ceramic, Stainless, Cast Iron

Or the like...?

https://www.dropbox.com/s/tsk9p1yywhsnq3g/CSI_Data_Validation_Source%20Basic%20Data%20DBx%20V1.xlsm?dl=0

Howard

Hmmm.... Stating which division for each seems tedious. Can it be set up so a set number of rows (i.e. 5 rows) are predefined for each Division and only the Sub division and Topics need to be chosen in the drop down lists. Also, the reason my original workbook looked the way it did is because explanatory text must be present. For example, If I choose Division 8 > Windows > Stainless Steel Windows .... I then must write an explanation under the chosen Topic like: "(2) 6' x 3' stainless steel windows, (4) 3' x 18" windows, all materials provided by owner, all labor provided by us."

Does that make sense? Also, there doesn't need to be a set price since we always get our updated price directly from the vendor when creating the bids.

Thanks for your help Howard.

-Amit

 

[L. Howard]

Hi Amit,

I think we can do what you ask.

I don't know what info should be where to do that. It's probably just a matter of readjusting the data you want to see within the rows/columns.

Maybe try this.

Delete the data in DIVISION's 4 through 16 (columns G over to S). Lets work with just three columns until we get a proper set up. Then we can do as many as you need.

In columns D, E, F Put the Sub Divisions you want to see in D1, E1, F1.
Then the five rows of Topics pertaining to the Sub Divisions's below in rows 2 to 5 or 6 or however many. These Topics don't have to be even in number of rows, one can have 3, the next 19 and the 5 etc., and I have a method to auto update the Topics items should they change, add to them or delete some.

If you can make those adjustments and post a link for your updated formatted workbook, along with what if's and questions and further suggestions then would be great.

I "cleaned" you data quite a bit, ridding it of all the underscores and blank cells, (which were showing blank but not responding as blank). Of course you may have the data appear as you want, for the most part, but at the moment we are working on concept. The final working data should be the work-place friendly and recognized stuff.

You can dump the column F $$ stuff and anything on sheet 2 and we're done with the $$'s.

Howard

 

[ghendi]

Hi Amit,

I think we can do what you ask.

I don't know what info should be where to do that. It's probably just a matter of readjusting the data you want to see within the rows/columns.

Maybe try this.

Delete the data in DIVISION's 4 through 16 (columns G over to S). Lets work with just three columns until we get a proper set up. Then we can do as many as you need.

In columns D, E, F Put the Sub Divisions you want to see in D1, E1, F1.
Then the five rows of Topics pertaining to the Sub Divisions's below in rows 2 to 5 or 6 or however many. These Topics don't have to be even in number of rows, one can have 3, the next 19 and the 5 etc., and I have a method to auto update the Topics items should they change, add to them or delete some.

If you can make those adjustments and post a link for your updated formatted workbook, along with what if's and questions and further suggestions then would be great.

I "cleaned" you data quite a bit, ridding it of all the underscores and blank cells, (which were showing blank but not responding as blank). Of course you may have the data appear as you want, for the most part, but at the moment we are working on concept. The final working data should be the work-place friendly and recognized stuff.

You can dump the column F $$ stuff and anything on sheet 2 and we're done with the $$'s.

Howard

HEllo Howard,

Thank you. Ok, I'll get on it. The underscored cells "___________" within the data were to separate different subtopics within the topic. FOr example, some of the topics have a <--- SUB TOPIC HERE ---> to label the following subtopics under it into a visual group for the person doing the bid. But some don't have groups since only one subtopic exists in that group, so instead I just put a ___________ to denote its own group without a subtopic group header.


-Amit