|
Use dynamic column reference without R1C1
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 |
Use dynamic column reference without R1C1
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) -- Biff Microsoft Excel MVP "Pontificateur" wrote in message ... 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 |
Use dynamic column reference without R1C1
Brilliant! And I was able to get rid of the "=" as you suggested.
Thanks much! "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Pontificateur" wrote in message ... 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 |
Use dynamic column reference without R1C1
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Pontificateur" wrote in message ... Brilliant! And I was able to get rid of the "=" as you suggested. Thanks much! "T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "Pontificateur" wrote in message ... 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 |
| All times are GMT +1. The time now is 09:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com