| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with changing column reference in formula
I have a long complex IF formula in which the following SUMPRODUCT formula is
contained. SUMPRODUCT(--(AE11:AE200)) Depending on the value of cell A1, I would like to dynamically change the column reference in the formula. For example: IF A1 = 1, THEN use the column reference AE in the formula IF A1 = 2, THEN use the column reference AF in the formula Is there a way to accomplish this by INDIRECT or another formula? Thanks! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with changing column reference in formula
Try
=SUMPRODUCT(--(OFFSET(AE9:AE18,0,A1-1)0)) Hope this helps, Hutch "GoBucks" wrote: I have a long complex IF formula in which the following SUMPRODUCT formula is contained. SUMPRODUCT(--(AE11:AE200)) Depending on the value of cell A1, I would like to dynamically change the column reference in the formula. For example: IF A1 = 1, THEN use the column reference AE in the formula IF A1 = 2, THEN use the column reference AF in the formula Is there a way to accomplish this by INDIRECT or another formula? Thanks! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with changing column reference in formula
Oops! Of course, I meant
=SUMPRODUCT(--(OFFSET(AE11:AE20,0,A1-1)0)) Hutch "Tom Hutchins" wrote: Try =SUMPRODUCT(--(OFFSET(AE9:AE18,0,A1-1)0)) Hope this helps, Hutch "GoBucks" wrote: I have a long complex IF formula in which the following SUMPRODUCT formula is contained. SUMPRODUCT(--(AE11:AE200)) Depending on the value of cell A1, I would like to dynamically change the column reference in the formula. For example: IF A1 = 1, THEN use the column reference AE in the formula IF A1 = 2, THEN use the column reference AF in the formula Is there a way to accomplish this by INDIRECT or another formula? Thanks! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with changing column reference in formula
Thank you Tom!!!
"Tom Hutchins" wrote: Oops! Of course, I meant =SUMPRODUCT(--(OFFSET(AE11:AE20,0,A1-1)0)) Hutch "Tom Hutchins" wrote: Try =SUMPRODUCT(--(OFFSET(AE9:AE18,0,A1-1)0)) Hope this helps, Hutch "GoBucks" wrote: I have a long complex IF formula in which the following SUMPRODUCT formula is contained. SUMPRODUCT(--(AE11:AE200)) Depending on the value of cell A1, I would like to dynamically change the column reference in the formula. For example: IF A1 = 1, THEN use the column reference AE in the formula IF A1 = 2, THEN use the column reference AF in the formula Is there a way to accomplish this by INDIRECT or another formula? Thanks! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with changing column reference in formula
Try this...
=COUNTIF(INDEX(AE11:AF20,,A1),"0") -- Biff Microsoft Excel MVP "GoBucks" wrote in message ... I have a long complex IF formula in which the following SUMPRODUCT formula is contained. SUMPRODUCT(--(AE11:AE200)) Depending on the value of cell A1, I would like to dynamically change the column reference in the formula. For example: IF A1 = 1, THEN use the column reference AE in the formula IF A1 = 2, THEN use the column reference AF in the formula Is there a way to accomplish this by INDIRECT or another formula? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Changing Column Reference in Macros? | Excel Discussion (Misc queries) | |||
| Excel - changing column reference based on value of other column | Excel Discussion (Misc queries) | |||
| MS Excel - changing reference column value based on another column | Excel Discussion (Misc queries) | |||
| Excel - changing column reference based on value of other column | Excel Discussion (Misc queries) | |||
| Stop cell reference in formula changing when insert column? | Excel Discussion (Misc queries) | |||
![GoBucks[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
