Data Validation looking up for a certain string in another cell and ignoring blanks

[Foxtrots]

Hi,

Been trying to create data validation in cell D3 which looks at cell C3 and then displays the relevant data validation options ignoring blanks if any.

Also is there a macro that can run when any information in cells A2 is changed then cells B2, C2 and D2 are deleted. If information in cells B2 is changed then cells C2 and D2 are deleted. If information in cells C2 is changed then cell D2 is deleted.

Please see the sample sheet, any help is much appreciated.

Thanks

 

[bosco_yip]

1] For easy checking I put 2 source tables and input table together in one worksheet.

2] See attached file with detail explanation.

Regards
Bosco

 

[Foxtrots]

Bosco,

Thank you very much for your help. works perfectly, spent a few hours trying to work it out.

Regards

 

[Foxtrots]

Hi Bosco and all the other experts,

Bosco, thanks for your help thus far.

In the sheet attached could some one please help with the following functions:

1) Amend the VBA worksheet code to delete only column G,H,I & L when column F is changed, currently it deletes columns J & K also. Would like to ignore columns J & K when changes are made to colums G,H,I & L.

2) Conditional Formatting (prefer VBA code) to turn the cell red in columns H & I if the value is not as per data validation, this to warn if user has pasted a value from elsewhere.

3)The sheet is not password protected as the table will not expand if protected, however with the sheet not protected a user can select all cells and untick the locked cell function, I have a code running to lock this sheet past a certain date but if the user changes the locked cell function then it will not work.
Either disabling the lock cell funtion or enabling the selection of all cells and then checking the lock cell funtion and finally locking the sheet past the date.

Please see the sheet and any help would be much appreciated.

Regards

Manu