Use dynamic column reference without R1C1

[Pontificateur]

Hi:
I'm trying to do the following:

ColA ColB
9 SUMIFS(Data!I:I,Data!C:C,"="&ProdYear,Data!G:G,"=" &ProdLine)
10 SUMIFS(Data!J:J,Data!C:C,"="&ProdYear,Data!G:G,"=" &ProdLine)
4 SUMIFS(Data!D,Data!C:C,"="&ProdYear,Data!G:G,"=" &ProdLine)
1 SUMIFS(Data!A:A,Data!C:C,"="&ProdYear,Data!G:G,"=" &ProdLine)
n SUMIFS(Data!n:n,Data!C:C,"="&ProdYear,Data!G:G,"=" &ProdLine)

Notice that the column on the leftmost Data!n:n parameter directly relates
to the number in colunn A. I would like the "n:n" to be dynamic, referencing
the number in A:A to get the proper column. Something like:
Data!column(A1):column(A1).

I don't want to change the sheet over to R1C1 notation if I can avoid it...
Can you help?
Thanks!

Greg

 

[T. Valko]

Try this:

Assume your entire range of use on Data is A:J

9 SUMIFS(Data!I:I,Data!C:C,"="&ProdYear,Data!G:G,"=" &ProdLine)

=SUMIFS(INDEX(Data!A:J,,A1),Data!C:C,"="&ProdYear,Data!G:G,"=" &ProdLine)

Where A1 = 9

Note that the column number is *relative* to the range.

You can probably also get rid of the "="& stuff:

=SUMIFS(INDEX(Data!A:J,,A1),Data!C:C,ProdYear,Data!G:G,ProdLine)

 

[Pontificateur]

Brilliant! And I was able to get rid of the "=" as you suggested.

Thanks much!

 

[T. Valko]

You're welcome. Thanks for the feedback!