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]

Also, do you have a sample of the data referred to in the drop downs, both primary and secondary?

I tried to transpose some of the data from the linked workbook, (to get it vertical) but got bogged down and was not sure I was getting it correct, and data had gaps and underscores within the data etc.

Say about three of four Divisions (or what ever you call them) of the main selection and the data that will relate to what the choices will be in the second drop down for whatever was selected in the first.

I would need them in columns vertically instead of across several columns in a single row. And on a new unformatted sheet to start with.

I believe that will be the driver on sheet lay out and once that is firmly established the bells and whistles can be added to suit.

Howard

 

[ghendi]

Also, do you have a sample of the data referred to in the drop downs, both primary and secondary?

I tried to transpose some of the data from the linked workbook, (to get it vertical) but got bogged down and was not sure I was getting it correct, and data had gaps and underscores within the data etc.

Say about three of four Divisions (or what ever you call them) of the main selection and the data that will relate to what the choices will be in the second drop down for whatever was selected in the first.

I would need them in columns vertically instead of across several columns in a single row. And on a new unformatted sheet to start with.

I believe that will be the driver on sheet lay out and once that is firmly established the bells and whistles can be added to suit.

Howard

Hello Howard,

Interesting... I'm not nearly as creative so it is hard for me to currently imagine what you are trying to do, but I do have a general picture.

For my data:
In the CSI DETAILED sheet where my data is held, Column A has the primary topics and then the secondaries for each primary are on the same row starting with Column B. The entire row, including the subtopic headers (i.e. <--- SUBTOPIC HEADER NAME --->) and the ________ that separate Subtopic Headers are included in the secondary list as organizers/spacers for the data to be displayed and seen correctly in the drop down format. Ill redo the data so it is in vertical format.

-Amit

 

[AkaTrouble]

reading the post you know the location of the data for the second drop down after the first drop down selection is made

could you not create the second drop down list so that it reads from a new range of blank cells exceeding the max number of possible results from first selection

on making first drop down selection you could run an event macro that would read the contents of the known data and populate the field range for the second drop down

this would work to refresh the second drop down if the first drop down is changed


not a coder or an expert just trying to help

 

[ghendi]

reading the post you know the location of the data for the second drop down after the first drop down selection is made

could you not create the second drop down list so that it reads from a new range of blank cells exceeding the max number of possible results from first selection

on making first drop down selection you could run an event macro that would read the contents of the known data and populate the field range for the second drop down

this would work to refresh the second drop down if the first drop down is changed


not a coder or an expert just trying to help

Hello AkaTrouble,

There might be a way, the problem is that I don't know how... I'm not savvy enough to understand how to do it. I can only see other people's code and adjust small things by tinkering around to try and suit to my tastes.

-Amit

 

[AkaTrouble]

you might want to give a little more info to help the guys here think

for example

if i have a first drop down selection list of 10 options

if item 1 is selected i want a second drop down option two to only show items from sheetname field a1 to a10

if item 2 is selected i want option 2 to show items from sheetname field b1 to b10

edit the text to highlight your need

i am understanding what you want but my skills are limited others will reply if they understand better

hope this helps

 

[L. Howard]

reading the post you know the location of the data for the second drop down after the first drop down selection is made

could you not create the second drop down list so that it reads from a new range of blank cells exceeding the max number of possible results from first selection

on making first drop down selection you could run an event macro that would read the contents of the known data and populate the field range for the second drop down

this would work to refresh the second drop down if the first drop down is changed


not a coder or an expert just trying to help

Hi akaTrouble,

Using a formula such as =OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A$1:$A$200),1) in the source window of the second drop down (or any drop down for that matter) would do the same thing without the need for code, if I under you correctly.

Where, the formula as shown would include entries for the drop down from A2 to A200, as they occur.

Stay tuned, may be looking for ideas further down the road.

Howard

 

[L. Howard]

you might want to give a little more info to help the guys here think

for example

if i have a first drop down selection list of 10 options

if item 1 is selected i want a second drop down option two to only show items from sheetname field a1 to a10

if item 2 is selected i want option 2 to show items from sheetname field b1 to b10

edit the text to highlight your need

i am understanding what you want but my skills are limited others will reply if they understand better

hope this helps

@ Akstrouble,

Me thinks you are on the right track, if you downloaded the workbook, looks like there are about 16 (maybe many more) first drop down items and way plenty for the second. Yet to be determined.

The OP also mentioned multiple selections for the second drop down on certain first drop down choices. That may be a challenge.

Howard

 

[AkaTrouble]

the challenge is to create the basic if drop down option 1 = selection one/two/three etc then populate option drop down 2 with these items

the coder only needs to provide the basic code based on basic input the end user can add or remove multiples of fields or contents to their own needs

and thanks for giving me more respect than i yet to think i have earned

i think the help should provided only needs to be a guide so the questioner has a solution not always to do it all for them

but i am new here and still learning

 

[ghendi]

the challenge is to create the basic if drop down option 1 = selection one/two/three etc then populate option drop down 2 with these items

the coder only needs to provide the basic code based on basic input the end user can add or remove multiples of fields or contents to their own needs

and thanks for giving me more respect than i yet to think i have earned

i think the help should provided only needs to be a guide so the questioner has a solution not always to do it all for them

but i am new here and still learning

Hello AkaTrouble,

I do agree with you. I am not one who supports spoon-feeding. Although, I am not asking for someone to do it for me and for me to sit back and relax and wait for an outcome (if that's what it is, I would pay for that service). Instead, I am trying to do what I can with my knowledge. I am happy to try and do things on my own, as I am currently, but I am by no mean savvy enough to be able to write it myself with just a written explanation of the functions that need to exist in order for it to work correctly; if that makes sense....

As of right now I am setting up the data into vertical columns and organizing it correctly so it may be easier to understand and to work with. I'll upload the updated sheet in a few minutes.

Thanks!

-Amit

 

[AkaTrouble]

ghendi

bad wording on my part never meant to imply your at fault in any way

was trying to feed the post to generalise it so others could offer you a general solution that you could modify to your exact needs

often on forums the original post gets lost in details when a general solution peeks original poster with more insight into problem to run and solve issue

forgive me if my input offered anything more than assistance towards a solution