data validation dropdown and disable cell

[Asdubai]

Hello, i would like to ask everyone's help regarding the following details;

data sheet
*if QUOTATION was selected in column A, column H,I AND J you can use the drop-down list and input the date and remarks
*if OTHERS was selected in column A, column H,I AND J will be disable and you cannot input any data

dashboard sheet
*under Due & Past Due column - the not started should not be counted in the dashboard
*under Due & Past Due column - the Work In progress should not be counted in the dashboard

i have attached the excel file for reference.

Thank you and apprciated your kind assistance.

Aileen

Attached Files

 

[Peter Bartholomew]

@Asdubai
I have made the validation in column H depend upon the content of column A. The key is introducing a Named Formula 'ValidationList' which can be one of two distinct ranges depending upon the description in column A.

I have yet to look at the dashboard.

 

[Peter Bartholomew]

I assume COUNTIFS will be used to populate the dashboard?

 

[Asdubai]

Hi peter, when i opened the files it is working but when i am trying to this in the other cell, i cannot do it , please explain to me what exactly i need to do. Thank you so much for helping me and looking forward to your response.

 

[Peter Bartholomew]

Hi Asdubai
The key is to go into Name Manager and look at the definition of the named formula 'ValidationList'.

You want the validation to vary row by row depending on the setting in the Description field. To do this, the formula uses a relative row reference
Table1[@[Job Pending Description]
and tests the content to see whether it is "Others".

Depending upon the result, the formula returns one of two alternative ranges (I have used the names '

activeL​

ist' and 'nullList' but you could use the direct range names of the form 'Sheet5!A1:A3' if you believe that is clearer.

To set up a new validation condition, first prepare the lists appropriate to the field. Then go into name manager and copy the formula for 'ValidationList'. Click 'New...' and set up another name, 'ValidationList2' (say). Paste the other formula into the 'Refers to:' dialogue box.

Edit the formula to reference the new ranges and adjust the condition (changing the cell reference and value as needed).

Finally, set up or modify the Settings in Data Validation to identify 'ValidationList2' as the Source of the allowed list.

 

[Asdubai]

Thank you so much, i got it!

i have additional problem and i hope you still have time to help me out.

i attached the file for your reference.

Again, thank you.