finding colum ref.

[justme1122]

Hi

Im a little bit stuck with he next step in the formular Im working on:

=SUM(IF(INDIRECT(J$3&"!$B:$B")=Members!$A4,INDIRECT(J$3&"!$M:$M"),0))

Im trying to make the red bit conditional, guess the long way to right it would be:

=if(J1=A1,A:A,if(J1=B1,B:B,if(J1=C1,C:C,............................

Could someone please suggest a formular to do this.

Thanks
Scott

 

[Airfix9]

Does J1 contain the column letter to look at then?

If so:


=SUM(IF(INDIRECT(J$3&"!"&J1&":"&J1)=Members!$A4,INDIRECT(J$3&"!$M:$M"),0))

 

[justme1122]

Thanks for the reply but its not quite that simple:

J1 if produced by a macro that lists sheet names.

sheets are imported by a macro.

Also to get J1 to = A1 (or any other) Ill need a left() function but I can work that bit out for myself.

 

[Oaktree]

Maybe:

=SUMIF(INDEX(INDIRECT(J$3&"!$A:$L"),0,MATCH(J1,$A$1:$A$12,0)),Members!$A4,INDIRECT(J$3&"!$M:$M"))

 

[justme1122]

Thanks for the reply oaktree but that isnt working for me.

Iv never used index/match but from the best I can tell there is no posiblilty for A:A to change to B:B in that formular.

 

[Oaktree]

...there is no posiblilty for A:A to change to B:B in that formular

Instead of your formula referencing the text "A:A" or "B:B", the INDEX(INDIRECT(J$3&"!$A:$L"),0,MATCH(J1,$A$1:$A$12,0)) construct will reference the 1st, 2nd, ..., 12th column according to where J1 is found in the range A1:A12.

Probably just needs a tweak to be MATCH(J1,$A$1:$L$1,0).