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

 

[VoG]

With the sheet name in B1 try like this

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

 

[iando99]

Hi VoG

thanks for the very quick reply, I've tried your idea and i get an error, the general Excel one that simply states

"the formula you've typed contains an error"

the code i tried was

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

it seems unhappy about the " after the &H4&.

 

[iando99]

sorry was in such a rush to try it i missed out half the ""'s

my fault i think it's worked, i'm going to do some testing

Thank you

 

[VoG]

It should be

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

 

[iando99]

Hi VoG

ok so i've tried your formula and i still get an error, just to make sure i'm doing this right, i have a defined name setup called "modules" the formula for it in name manager is

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

as soon as i press ok to accept it, the formula changes to

=INDEX(Table1,0,MATCH("'"&'P018-1'!C19&"'!$C5",Table1[#Headers],0))

and stops working.

i tried doing data validation, selecting list and entering the formula directly in there however i get the standard Excel error.

is it right that the $C20 should be inside the quotations as it is the cell used in the initial drop down list, not the sheet name?

thank you

 

[VoG]

What does

="'"&H4&"'!$C20"

return. Does that look like a valid reference? If so then wrapping it in INDIRECT() should work in your formula.

 

[iando99]

i get 'P018-1'!$C20

in the cell as a result of
="'"&H4&"'!$C20"

basically the sheet name + the Cell my initial drop down list is in

 

[VoG]

INDIRECT works for me in a simple formula

Excel Workbook
	C
20	Fred
P018-1

Excel Workbook
	H	I
4	P018-1	Fred
Sheet6

 

[iando99]

would this be easier if i sent you a copy of the spread sheet? then you could see the problem i'm having?