INDEX & MATCH function using Dynamic worksheet names

[iando99]

hi Guys

thanks for reading my post. OK so i'm slowly losing the will to live!
I have an excel workbook with a dynamic dependent drop down list setup, works a treat on the first worksheet i set it up on.

however here's the problem, my workbook contains a good handful of worksheets each named to reflect the certificate on the page, i need the drop down list to work on each tab. problem is when i setup the index/match function it requires a specific sheet name to make it work i.e.

=INDEX(Table1,0,MATCH(PI!$C20,Table1[#Headers],0))

however what i need it to do is reference the worksheet name, as new ones get added and names may change, something like

=INDEX(Table1,0,MATCH(&ActiveSheet.Name&$C20,Table1[#Headers],0))

however this doesn't work, i do have a cell on each page that holds the Sheet name using the function:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

but i cant even get it to use that cell as a reference in my formula.

any ideas would be fantastic around now


thank you

Ian

 

[iando99]

ok so the latest is if i use the following formula as a defiend name function

=INDEX(Table1,0,MATCH(INDIRECT("'"&H4&"'!$C20"),Table1[#Headers],0))

as soon as i save it the &H4& reverts to the sheet name, problem is i still have to manually create another defined name for each sheet.