Data Validation for each cell in a matrix - Need Help!

[HM1961]

Hi
I joined the forum today and Nice to meet You All.
I am from Sydney Australia.

I have an array of 3 rates based on material types.
3 Columns indicate dollar rates for a specified unit of materials.

The user needs to select any one cell against the three.
When they decide the material type (one out of the Three), the other two cells have to be made to zero.

Then the next column Qty (Quantity) where the Qty is to be entered.
using the Data validation, I manually created 'List' based data validation to choose the value. (adding a 0.00 to the list in a separate sheet).

This is laborious and time consuming for me.

Can someone suggest, Is there any way to do the same for a range of cells (3 x say 25).

I need to understand and extend this to other cells in the sheet.

Anxiously looking forward to some direction.

Thanks
HM1961

[BadlySpelledBuoy]

Hi and welcome.

It would be much easier for us to help you if we could see the issue in context. So if you can attach a sample workbook showing before and after (manually generated) it would be great.

Click on GO ADVANCED (below your reply) and then scroll down to Manage Attachments to open the upload window. Should be straightforward from there.

BSB

[HM1961]

Hi BadlySpelledBuoy,

Thanks for promptly replying and assisting me to get across.


I have attached the spreadsheet 'Sample Pricing Sheet-HM1961'.

I have provided the narration on the table header.

Cheers

HM1961.

[BadlySpelledBuoy]

Are you tied to this approach?

Perhaps it would be easier to have a column with a dropdown that could be applied to all rows where the user selects A, B or C. It then looks up the value for the relevant material in the hidden list and makes the other two zero.

Would that work at all?

If you are tied to this approach then perhaps a short piece of VBA could loop through the rows and apply the validation rather than you doing it manually.

BSB

[Glenn Kennedy]

Maybe... delete the values in CD & E... Replace the DV with =J8 and copy down the row

[BadlySpelledBuoy]

Whilst I prefer Glenn's approach, I'm attaching this purely because I've put it together.
Changing the material selection in the new column will also negate the need for the user to set other columns to zero if they change the selection.

BSB

[HM1961]

Hi Glenn and BSB,

Thanks a Ton for your timely awesome help.

My reply to the two scenarios is:-
a) Glenn's solution is apt except that if the user erroneously selects a material, there is no provision to choose another one.
b) BSB's list of populating works perfectly fine. But

As per the procurement policies, The approved rates cannot be hidden as it has to be transparent for the management to have an option to choose the material on a case to case basis depending on the project works.

Glenn is there something that I can do to include changing the options to other, But ONLY ONE Material.

Looking forward to your valuable response.
HM1961

[Glenn Kennedy]

If you erroneously choose a material... hit delete.

[BadlySpelledBuoy]

As per the procurement policies, The approved rates cannot be hidden as it has to be transparent for the management to have an option to choose the material on a case to case basis depending on the project works.

I'm not sure what this means... I only mentioned having the list hidden away as you said it would be in your attachment.

BSB

[HM1961]

Hi Glenn and BSB,

I wish to thank you both for the excellent help.

How do I indicate 'Solved'?

Cheers
HM1961

[HM1961]

I figured out how to mark this thread as "Solved".

HM1961

[BadlySpelledBuoy]

You should see "Thread Tools" above your original post. You can select mark as solved from there.

BSB