JohnDouglas
Board Regular
- Joined
- Jan 5, 2005
- Messages
- 239
I have the following formula where A4 is the name of the sheet to be referenced:
=INDEX(INDIRECT("'"&$A4&"'!$D$2:$AJ$101"),MATCH(CONCATENATE($B$1,B$3),INDIRECT("'"&$A4&"'!$A$2:$A$101"),0),MATCH($E$1,INDIRECT("'"&$A4&"'!$D$1:$AJ$1"),0))
the $A$2:$A$101 part references a column in another sheet which is the result of concatenating columns B2:B101 & C2:C101 is placed.
If possible i'd love to get rid of the column that does that concatenation and instead get my forumla pasted above to do the concatenation automatically. Whenever i try i get an error. Can anyone help me out and give me the correct way of getting this forumla to concatenate mid flow?!
I tried:
=INDEX(INDIRECT("'"&$A4&"'!$D$2:$AJ$101"),MATCH(CONCATENATE($B$1,B$3),INDIRECT("'"&$A4&"'!(concatenate($B$2:$B$101,$C$2:$C$101))"),0),MATCH($E$1,INDIRECT("'"&$A4&"'!$D$1:$AJ$1"),0))
but sadly no dice!
=INDEX(INDIRECT("'"&$A4&"'!$D$2:$AJ$101"),MATCH(CONCATENATE($B$1,B$3),INDIRECT("'"&$A4&"'!$A$2:$A$101"),0),MATCH($E$1,INDIRECT("'"&$A4&"'!$D$1:$AJ$1"),0))
the $A$2:$A$101 part references a column in another sheet which is the result of concatenating columns B2:B101 & C2:C101 is placed.
If possible i'd love to get rid of the column that does that concatenation and instead get my forumla pasted above to do the concatenation automatically. Whenever i try i get an error. Can anyone help me out and give me the correct way of getting this forumla to concatenate mid flow?!
I tried:
=INDEX(INDIRECT("'"&$A4&"'!$D$2:$AJ$101"),MATCH(CONCATENATE($B$1,B$3),INDIRECT("'"&$A4&"'!(concatenate($B$2:$B$101,$C$2:$C$101))"),0),MATCH($E$1,INDIRECT("'"&$A4&"'!$D$1:$AJ$1"),0))
but sadly no dice!