Building on
xor's work, column E of the
Style Detail sheet in the attached eliminates VLookup.
Note a variation of
xor's formula has been used (makes no difference to the results in this case):
=VLOOKUP($B6,INDIRECT("'"&INDEX(WSList,MATCH(1,--(COUNTIF(INDIRECT("'"&WSList&"'!$A$6:$
B$31"),$B6)>0),0))&"'!$A$6:$B$31"),2,FALSE)
to:
=VLOOKUP($B6,INDIRECT("'"&INDEX(WSList,MATCH(1,--(COUNTIF(INDIRECT("'"&WSList&"'!$A$6:$
A$31"),$B6)>0),0))&"'!$A$6:$B$31"),2,FALSE)
edit:
after discovering hidden sheets with a different arrangement of columns the new formula would be, in C6:
Code:
=INDEX(INDIRECT("'"&INDEX(WSList,MATCH(1,--(COUNTIF(INDIRECT("'"&WSList&"'!$C$6:$C$40"),$B6)>0),0))&"'!$E$6:$E$40"),
MATCH($B6,INDIRECT("'"&INDEX(WSList,MATCH(1,--(COUNTIF(INDIRECT("'"&WSList&"'!$C$6:$C$40"),$B6)>0),0))&"'!$C$6:$C$40"),0))
(array-entered, copied down)