How to get dynamic Unique Values in combobox while creating Estimate Sheet.

[sumitbansal]

Hi,

I have a worksheet where user creates an estimate for the amount of building material required for construction purpose as per the customer requirements. For each estimate entry, has to get the quantity of material required, calculated as per dimensions of work and amount of work [which user enters in another sheet (Abstract of Quantity). There may be one entry or more for each estimate entry]. Simply put, for each "Estimate" entry there will be at least "one" "Abstract of quantity" entry. Now user wants that whenever, he is entering data in the "Abstract of Quantity" sheet, he should get a drop-down combo-box which should get populated dynamically with all the unique entries for all the "Abstract of Quantity" sheet entries related to that entire estimate; and whichever entry he then selects gets filled with the last entry of that unique entry previously done.


Example: The user is entering data for item number 5 of the "Estimate" Sheet in the "Abstract of Quantity" Sheet. The drop-down combo-box in "Abstract of Quantity" Sheet should show all the unique "Abstract of Quantity" entries previously done for other 4 items of the "Estimate" Sheet along with all the unique entries of present "Abstract of Quantity" for item number 5.


This is exactly what I am not able to do. Please help me with this. Also, how to attach a workbook with this message??


Thanks,

Sumit.

 

[sumitbansal]

Just found the way to upload files...


Here is the link to the file. I hope it will provide better understanding...


https://docs.google.com/file/d/0B_wsT6a6FzOnRVRjZWFnUll1UTQ/edit?usp=sharing

 

[sumitbansal]

Its been a week and no one has replied... does this mean that what I am trying to achieve is un-achievable??

 

[NARAYANK991]

Hi Sumit ,


There can be many reasons why a question remains unanswered ; one of them is there is no answer.


It can also happen that an incomplete question puts off those who would like to answer ; but , given the kind of information that is missing , the amount of effort that will have to be put in to get that information ... you get the idea.


Uploading a file is a big facilitator ; but not sufficient. What is required is a clear , detailed specification of what you want.


In your specific case , let us consider your requirement :



Now user wants that whenever, he is entering data in the "Abstract of Quantity" sheet, he should get a drop-down combo-box which should get populated dynamically with all the unique entries for all the "Abstract of Quantity" sheet entries related to that entire estimate; and whichever entry he then selects gets filled with the last entry of that unique entry previously done.




I have absolutely no idea of what that means.


1. In which cell will the user enter data ?


2. Do you really mean a drop-down combobox or a data validation drop-down ?


3. From which range , with specific cell addresses e.g. G17:G33 , will the combobox have to take data ?


4. What does the last part of the above paragraph mean ? Can you explain with an example ?



and whichever entry he then selects gets filled with the last entry of that unique entry previously done.


Narayan

 

[sumitbansal]

Thanks for the reply...

Yes, it is difficult to understand the requirement if the question is incomplete. I apologize for that. I will complete it now. Let's start from start...


1. When we open the file, it opens the splash screen where it asks the user to choose an option. We choose option no. 1

2. It then asks for certain input and then it goes to Op_estimate sheet where the user has to "Add Item", by clicking on the "Add Item" button.

3. As the user adds item, (Say 10.11), a macro runs and user selects his options.

4. Column H shows Quantity, by default which is 1, but user can get it calculated if he is not certain about it by double clicking in that respective Cell in Column H.

5. When the user double clicks in column H, he is taken to yet another sheet "Abstract of Quantities" where he then add the required construct work to be done so that the quantity of raw construction material can be calculated. Eg. in Description = Wall2; in No.= 2; then the dimensions in Length-Breadth-Height format and the quantity get calculated.

Please note that the data can be repeated. Like first we enter Wall2 to calculate the amount of concrete required, then again we can enter Wall2 to calculate the amount of PoP required and then again to calculate the amount of Paint Required and so on..

6. Also, take note that for each single Entry in "Op_Estimate" Sheet, the user can fill upto 100 entries in "Abstract of Quantities" Sheet and thus All those entries in "Abstract of Quantities" sheet are used to calculate the exact quantity required for that one particular Entry of the "Op_Estimate".

7. It if is first entry of the estimate AND first entry of "Abstract of Quantities" Sheet, then it is okay, however, if it is not then a drop-down combobox (data validation drop-down might also work) should appear on double clicking on the Description field (Columnn F) of "Abstract of Quantities" Sheet and it should provide the list of unique entries done previously for all Estimate Entries related to that Estimate.


Example.

The user is now entering data for item number 1 of the "op_Estimate" Sheet in the "Abstract of Quantities" Sheet.

if it is item number 1 in the "Abstract of Quantities" sheet, then no drop down box is required.

if it is second (or more) item of the "Abstract of Quantities" then the drop-down combo-box (or validation drop down) should show all the previous unique entries done in he "Abstract of Quantities" Sheet.

However, if the user is entering the Second Entry (or more)in the "Op_Estimate" Sheet and it is the first entry in the "Abstract of Quantities" sheet, then even the first entry should show all the unique records of the "Abstract of Quantities" data filled earlier for item number 1 of "Op_Estimate" sheet.

And if there is second entry (or more) for "Abstract of Quantities" sheet, then the data of this Abstract of quantities should also be included in the drop down box (data validation drop down)

And when user selects any value from that drop down, the entire row should get filled by the latest entry for that item previously filled.


I hope it is not too confusing, however, i would be more than willing to elaborate more there it is required.


Thanks again for the reply.

Sumit.