Concatanation formula within a formula ... any ideas please?

[JohnDouglas]

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!

 

[Aladin Akyurek]

Re: Concatanation formula within a formula ... any ideas ple

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!

That would produce an array object that INDIRECT cannot process.

 

[JohnDouglas]

so there is no way that i can do this then?

 

[Aladin Akyurek]

so there is no way that i can do this then?

You already have a fairly expensive formula, one that is prone to degrade the performance. The following is bound to worsen it....

=INDEX(INDIRECT("'"&$A4&"'!$D$2:$AJ$101"),MATCH($B$1&","&B$3),INDIRECT("'"&$A4&"'!B2:B101")&","&INDIRECT("'"&$A4&"'!C2:C101"),0),MATCH($E$1,INDIRECT("'"&$A4&"'!$D$1:$AJ$1"),0))

which you must confirm with control+shift+enter instead of just with enter.

 

[JohnDouglas]

Many thanks for your help aladin. This worked fantastically.