C
Conan Kelly
Hello all,
It appears that the INDIRECT() function changes the calling cell.
Let me try to explain:
I have a dynamic named range "NR6.PerMonth":
- When "=NR6.PerMonth" is in cell C27 on the "Summary" sheet, it will
refer to/return cell C982 on the "6" sheet.
- When "=NR6.PerMonth" is in cell D27, it will refer to/return cell
D982....and so on and so forth, going across.
BUT!!! (A26 on the "Summary" sheet is a formula that returns the number
6...A982 on the "6" sheet is a label):
- When '=INDIRECT("NR" & $A26 & ".PerMonth")' is in cell C27 on the
"Summary" sheet, it will refer to/return cell A982 on the "6" sheet.
- When '=INDIRECT("NR" & $A26 & ".PerMonth")' is in cell D27, it will
refer to/return cell A982....and so on and so forth, going across.
....so, in other words, INDIRECT() changes the calling cell from C27/D27 to
A27 (or maybe it is A26) and it always returns A982.
Does that sound right? Is there a setting or a work-around so that
INDIRECT() will not change the calling cell?
Thanks for any help anyone can provide,
Conan Kelly
---------------------------
"Smokin' weed kills your brain cells. Drinkin' only screws up your
liver...ya got 2 a those."
- Earl Hickey (NBC's "My Name is Earl")
If Milli Vanilli falls in the woods, does someone else make a sound?
It appears that the INDIRECT() function changes the calling cell.
Let me try to explain:
I have a dynamic named range "NR6.PerMonth":
- When "=NR6.PerMonth" is in cell C27 on the "Summary" sheet, it will
refer to/return cell C982 on the "6" sheet.
- When "=NR6.PerMonth" is in cell D27, it will refer to/return cell
D982....and so on and so forth, going across.
BUT!!! (A26 on the "Summary" sheet is a formula that returns the number
6...A982 on the "6" sheet is a label):
- When '=INDIRECT("NR" & $A26 & ".PerMonth")' is in cell C27 on the
"Summary" sheet, it will refer to/return cell A982 on the "6" sheet.
- When '=INDIRECT("NR" & $A26 & ".PerMonth")' is in cell D27, it will
refer to/return cell A982....and so on and so forth, going across.
....so, in other words, INDIRECT() changes the calling cell from C27/D27 to
A27 (or maybe it is A26) and it always returns A982.
Does that sound right? Is there a setting or a work-around so that
INDIRECT() will not change the calling cell?
Thanks for any help anyone can provide,
Conan Kelly
---------------------------
"Smokin' weed kills your brain cells. Drinkin' only screws up your
liver...ya got 2 a those."
- Earl Hickey (NBC's "My Name is Earl")
If Milli Vanilli falls in the woods, does someone else make a sound?