Validate / insert value based on another sheet of excel

hi,
I want master sheet blank column to be updated by the data given in ADG sheet.
And in ADG sheet, Name(Column B) to be updated by Mastersheet "Name" column with reference of "Reference id" of ADG sheet . If there is any mismatch in column B (name) & C (name2) in ADG sheet ,Cells of column C gets highlighted with different color.

and last can I get all the data automatically in MDG sheet with reference of master sheet if i enter MDG in Row b2 of MDG sheet..
I ha posted the same problem on Mrexcel forum but i did not find that forum useful.

thanks in advance

Hello COOL_GAVI and Welcome to Excel Forum.
As to the request in the first paragraph:
On the master sheet:
Paste the following into cell D2 and drag the fill handle down and over to cell F16: =INDEX(ADG!E$3:E$17,MATCH($C2,ADG!$D$3:$D$17,0))
On the ADG sheet:
Paste the following into cell B3 and copy down: =INDEX('MASTER SHEET'!B$2:B$16,MATCH(D3,'MASTER SHEET'!C$2:C$16,0))
Select cells C3:C17 and apply the following formula as a conditional formatting rule: =C3<>B3
As to the second paragraph, I am not sure that I understand what is meant by: "if i enter MDG in Row b2 of MDG sheet".
I am assuming that it means that if MDG is in cell B1 then only data matching Ref. Code should be displayed.
If that is correct then paste the following array entered formula into cell B3 and after activating**, drag the fill handle over and down:

Formula:

Please Login or Register  to view this content.

**Array formulas are not activated in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.