| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| R1C1 reference style | Excel Discussion (Misc queries) | |||
| R1C1 Reference Style won't STAY gone? | Excel Discussion (Misc queries) | |||
| I switched on the R1C1 reference style but I can't switch it off a | Excel Discussion (Misc queries) | |||
| R1C1 reference style | Excel Discussion (Misc queries) | |||
| R1C1 reference | Excel Discussion (Misc queries) | |||
Hybrid Mode
