Update data in sheet from another sheet when the relevant value is entered into a cell

you probably need to use a vlookup or index/match, but without seeing what you are working with, its hard to say. as suggested. you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

try this. Note though, that it would be better if you had consistent headings for rows and columns...
=INDEX(Sheet1!$A$5:$M$17,MATCH("% of "&Sheet2!$A22,Sheet1!$A$5:$A$17,0),MATCH(Sheet2!$A$21,Sheet1!$A$5:$M$5,0))

if you make you're row headings the same (Dark becomes % of Dark), then you can use this instead...
=INDEX(Sheet1!$A$5:$M$17,MATCH(Sheet2!$A26,Sheet1!$A$5:$A$17,0),MATCH(Sheet2!$A$21,Sheet1!$A$5:$M$5,0))

Yes, start in B22. if you need help with the formulas in D, let me know

I just opened you're file again, and copied my formula (the 1st 1) into B22 on sheet2, and ity worke4d fine?

see the attached, perhaps you have changed you're file layout after you uploaded?

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neet and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)