in cell 'NC-40'!A2 (which is a merged cell, which is a venial crime):
=LOOKUP(2^20,INDEX('Input List'!$E$4:$N$36,MATCH('NC-40'!A1,'Input List'!$A$4:$A$36,0),0),'Input List'!$E$3:$N$3)&" "&LOOKUP(2^20,INDEX('Input List'!$E$4:$N$36,MATCH('NC-40'!A1,'Input List'!$A$4:$A$36,0),0))
this will just show 'Brown 1' etc., and not colour the cell.
encase the above formula as IFERROR(above_formula,0) in case you do not want to see #N/A errors.
UPDATE:
use a set of Conditional Formatting rules to colour result cells.
if you have 2007 or higher, you could use this method.
highlight cells A1 through J18 -- after you have executed the above formula in appropriate cells. then, navigate to:
Home > Conditional Formatting > Highlight Cells Rules > Text That Contains
in the resulting box, type "Brown" (without the quotes). in the adjacent drop-down, select Custom and setup a formatting. do this for each colour that you want to setup, and for each page that you want to setup CF for.
Bookmarks