Vlookup with index match but on sheet names

Hi Guys,

Hope all are well.

There is a macro within this workbook (on the summary sheet tab) which places all sheet names in col A, from a2 down.

I need to adapt the index match formula in cells B and C2 to be able to reference the values in col A, which are the sheet names, as I don’t want to have to type the sheet name each time!

=INDEX(Bob!$B:$B,MATCH(Summary!$B$1,Bob!$A:$A,0))

in the formula above I typed Bob! which is in cell a2 of the summary sheet but would like to be able to reference it using a formula.

Any ideas?

please see attached.

Thanks