How to attatch different cells to the value on a single cell ??

[shshank211090]

Hi Friends !!

I've got this small problem that is eating my head.

I have created a data validation list in one cell and I want that as soon as i select some option within the list,
some cells should get filled in with the values from a range in another worksheet.
Suppose, I choose fruits from my main list in column A. Now I want that in Column B, all the cells under the range "fruits" should get pasted there automatically, how much ever the number of cells may be,i.e, it should be dynamic.

Please assist me on this. The matter is very urgent.

Thanks and Regards

Shshank

[dilipandey]

Hi Shshank,

Welcome to the forum.

I guess you need the multiple lookups i.e., if you select fruit (in a validation list) then the next column (B) will show all the entries against fruit.
Something like:-
Vegetable onion
fruit apple
fruit orange
vegetable carrot
fruit mango

and you need apple, orange, mango in column B if you select fruit in the cell A1 validation.

It will be more clear if you can upload a sample workbook. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[shshank211090]

Thanks for your reply!!!

I have attached the file with this post. Just tell me some method by which I can take the values from B29 and C29 of Data sheet ,i.e, the 'ConcreteLabor' range and put that into the the cells under the Labor Description Column. See, I want it to be dynamic in nature , as in whatever type of mason i keep on adding in the ConcreteLabor range, that should automatically get displayed in the Labor Description Column once I select Concrete from the list in cell B10. I could do it using OFFSET function but for that I need to first select the exact no of cells in the column as the no of cells in ConcreteLabor range, which I need to do everytime. So, Please tell me as soon as possible how it can display it automatically.

Waiting eagerly for your reply,

Shshank

[dilipandey]

Hi Shshank,

Still not very clear to me.
You need to extract the values from B29 and C29 only.. not sure the logic as it will have only two values either Mason or MC ?
Not able to locate the Labor Description column..

Would suggest you to highlight the area where you need help in the worksheet and enter manually what you want to achieve and then I will try to obtain that using logics / formulas. Also I believe there are named ranges used, not sure if this is something to do here as well.

Regards,
DILIPandey

<click on below 'star' if this helps>

[shshank211090]

Sir,

I want the values in H column starting from the 10th row, i.e, the row corresponding to the cell with the list,i.e, B10.

Sir, I want that when I select concrete from the list of options in B10, automatically "Mason" and "MC" should go to cells H10 and H11 respectively.

[dilipandey]

Dear Shashank,

Which factor (logic) is driving that if B10 is concrete then H10 would be Mason and H11 would be MC.
Not sure if you wish to enter this manually (hard coded) or is there any master reference table as well, seeing your efforts in the sheet hints me that there should be some criteria as well.

Regards,
DILIPandey

<click on below 'star' if this helps>

[shshank211090]

Sir,

Thank you again.

Sir, I'm developing this new system in which I want that when I select some option from the list in Column B, automatically excel should give me the different items or materials required for the respective item. And when I put the quantity to be done, it should give me the quantity of the respecitve items and materials to be used in performing the job.

Like I select Concrete in Column B, automatically Mason and MC should appear in Column H and when I put the quantity, say, 10 Cubic Metre, it should give me the no of labor required for doing that. You just tell me how to bring those 'Mason and MC' in Column H automatically, rest all I got.

I guess, things will be much clear to you.

Waiting for your reply,
Shshank Mattoo

[shshank211090]

And all those items ,such as Mason and MC, I'm putting in a separate sheet. I want it to bring the values from there and put here.

[dilipandey]

Dear Shshank,

Like I select Concrete in Column B, automatically Mason and MC should appear in Column H and when I put the quantity, say, 10 Cubic Metre, it should give me the no of labor required for doing that. You just tell me how to bring those 'Mason and MC' in Column H automatically, rest all I got.

Considering worksheet "Data" and basis on your above comment, I am having below queries:-
1) Where you want to select "Concrete" in column B - be specific? Also I am not able to see any drop down list (data validation). You want user to select or type "Concrete"?
2) Mason and MC will appear automatically - but where you want Excel to look in for those values (you said in later post that you are putting these in a separate sheet but I am not able to find that sheet, if it is missing; please upload correct workbook.
3) Also where you want Mason and MC to appear i.e., cell location in column H. I guess this should in the right side of similar row where you would select Concrete in Col B.
4) Similarly is there any table from where the quantity is to be picked up or do you want it to be first calculate and then get displayed. In either of the cases, calculation logic should be provided.

Also the sheet is kind of messed up with formula entered as texts and formatting is not good to provide the correct display. Since you are working on this sheet and other many not be at your level of understanding, hence you need to be specific and detailed. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[shshank211090]

Forum.txtNew Format-Forum.xls

Sir,

Here is a detailed explanation.

Thanking You,

Shshank Mattoo

[shshank211090]

Sir,

I'm still waiting.

Regards,
Shshank Mattoo

[dilipandey]

Hi Shshank,

I am litle occupied in some other work, will revert on this after 10 PM IST.

Regards,
DILIPandey

<click on below 'star' if this helps>

[dilipandey]

HI Shshank,

Gone through the two attachments (text file and excel)

You said
"
1) Put Labor types like Mason and MC here, in green color in Backup File,
to the Column I and the cells in Blue color.
"

We can put Mason and MC here but is there any criteria or logic, after which these two entries to be filled in?
Also I am not able to see the blue color.

It looks like you are preparing a entire template for some construction work, suggest you to break down your entire project piece by piece (phase by phase) and come with one specific query at a time. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>