Use dynamic column reference without R1C1

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pontificateur - 04 Feb 2008 19:16 GMT

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: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

Reply to this Message

T. Valko - 04 Feb 2008 19:32 GMT

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)

Signature

Biff
Microsoft Excel MVP

> Hi:
> I'm trying to do the following:
[quoted text clipped - 18 lines]
>
> Greg

Reply to this Message

Pontificateur - 04 Feb 2008 22:34 GMT

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

Thanks much!

> Try this:
>
[quoted text clipped - 34 lines]
> >
> > Greg

Reply to this Message

T. Valko - 04 Feb 2008 22:55 GMT

You're welcome. Thanks for the feedback!

Signature

Biff
Microsoft Excel MVP

> Brilliant! And I was able to get rid of the "=" as you suggested.
>
[quoted text clipped - 39 lines]
>> >
>> > Greg

Reply to this Message