INDEX MATCH - INDIRECT cell reference.

[LeeBillington]

Hi guys, anyway someone would be able to change this to INDIRECT which references the worksheet to target in a 'Register'!$K$23?

Please Login or Register  to view this content.

So where it says 'Chiller PM' instead of it being that. instead being referenced to K23 on sheet 'Register'. (Which will be the sheet to target in K23)

similar to this other one im using in a way.

Please Login or Register  to view this content.

Thanks

[Richard Buttrey]

Presumably

=(IFERROR(INDEX(Indirect("'"&K23&"'!"K23)&......blah blah

[LeeBillington]

I tried doing it myself and couldn't do it.. kept messing it up somehow. hence why i asked if someone could? sorry if i didn't explain that.

[Jonmo1]

Try

=(IFERROR(INDEX(INDIRECT("'"&Register!$K$23&"'!$G$1:$XFD$1"),SMALL(IF(INDEX(INDIRECT("'"&Register!$K$23&"'!$G$31:$XFD$1900"),MATCH('Holiday - Registers'!$C$3,INDIRECT("'"&Register!$K$23&"'!$C$31:$C$1900"),0),)>0,COLUMN($G:$XFD)-6),ROWS($1:2))),""))

[LeeBillington]

Worked Perfectly, thanks jonmo1

[Jonmo1]

You're welcome.