change operating cells dynamically according to the value of a fixed cell
 

Hi all,

I'm experiencing a tough MS excel function problem. Please give me
some advices.

-------------------------------------
a b c d
1 3 9 50
2 10 8 40
3 7 30
4 6 20
5 5 10
.. . .
.. . .
.. . .
-------------------------------------

I need to put my results in column D. Currently, D1 should euqal to
"if(C1-SUM(B1:B3)C3,C1-B1,C1-B1+$A$2)". That means I need to make a
justification that whether C1 is bigger than the summary of B1 to B3.
If it's smaller than the sum, I need to add a constant($A$2) to D1
while reducing B1. Then D2=if(C2-SUM(B2:B4)C4,C2-B2,C2-B2+$A$2), and
so on.
But, the problem is that the $A$1=3 is NOT a constant number!!! For
different products, this A1 is different. Perhaps it's 6 sometimes, so
the D1 changes to "if(C1-SUM(B1:B6)C6,C1-B1,C1-B1+$A$2)". Since
there're hundreds of cases to deal with, certainly I don't want to
change the formulas again and again.

To be explicit, I need to change the formulas in column D, according
to the value of $A$1. If
$A$1=3,D1="if(C1-SUM(B1:B3)C3,C1-B1,C1-B1+$A$2)",
D2="if(C2-SUM(B2:B4)C4,C2-B2,C2-B2+$A$2)"...
When $A$1=4, D1="if(C1-SUM(B1:B4)C4,C1-B1,C1-B1+$A$2)",
D2="if(C2-SUM(B2:B5)C5,C2-B2,C2-B2+$A$2)"...

To be more simplified, I need to firstly get the value of $A$1, for
example $A$1=6, then I cite this value to be a mark for another cell,
for instance, $B$6. But it's NOT always 6, it's changing according to
different $A$1. However, I tried to adopt something like $B$($A$1),
but it's not possible.

Would you please help me to resolve the trouble? Thank you very much.

zh.y

zh.y,

You could try (along the lines of your idea of using $B$($A$1)

INDIRECT("$B$" & $A$1)

HTH,
Bernie
MS Excel MVP

"Zuohong" wrote in message
om...
Hi all,

I'm experiencing a tough MS excel function problem. Please give me
some advices.

-------------------------------------
a b c d
1 3 9 50
2 10 8 40
3 7 30
4 6 20
5 5 10
. . .
. . .
. . .
-------------------------------------

I need to put my results in column D. Currently, D1 should euqal to
"if(C1-SUM(B1:B3)C3,C1-B1,C1-B1+$A$2)". That means I need to make a
justification that whether C1 is bigger than the summary of B1 to B3.
If it's smaller than the sum, I need to add a constant($A$2) to D1
while reducing B1. Then D2=if(C2-SUM(B2:B4)C4,C2-B2,C2-B2+$A$2), and
so on.
But, the problem is that the $A$1=3 is NOT a constant number!!! For
different products, this A1 is different. Perhaps it's 6 sometimes, so
the D1 changes to "if(C1-SUM(B1:B6)C6,C1-B1,C1-B1+$A$2)". Since
there're hundreds of cases to deal with, certainly I don't want to
change the formulas again and again.

To be explicit, I need to change the formulas in column D, according
to the value of $A$1. If
$A$1=3,D1="if(C1-SUM(B1:B3)C3,C1-B1,C1-B1+$A$2)",
D2="if(C2-SUM(B2:B4)C4,C2-B2,C2-B2+$A$2)"...
When $A$1=4, D1="if(C1-SUM(B1:B4)C4,C1-B1,C1-B1+$A$2)",
D2="if(C2-SUM(B2:B5)C5,C2-B2,C2-B2+$A$2)"...

To be more simplified, I need to firstly get the value of $A$1, for
example $A$1=6, then I cite this value to be a mark for another cell,
for instance, $B$6. But it's NOT always 6, it's changing according to
different $A$1. However, I tried to adopt something like $B$($A$1),
but it's not possible.

Would you please help me to resolve the trouble? Thank you very much.

zh.y

Hey Bernie, thank you for the great help. Now I could continue my thinking.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ...
zh.y,

You could try (along the lines of your idea of using $B$($A$1)

INDIRECT("$B$" & $A$1)

HTH,
Bernie
MS Excel MVP

"Zuohong" wrote in message
om...
Hi all,

I'm experiencing a tough MS excel function problem. Please give me
some advices.

-------------------------------------
a b c d
1 3 9 50
2 10 8 40
3 7 30
4 6 20
5 5 10
. . .
. . .
. . .
-------------------------------------

I need to put my results in column D. Currently, D1 should euqal to
"if(C1-SUM(B1:B3)C3,C1-B1,C1-B1+$A$2)". That means I need to make a
justification that whether C1 is bigger than the summary of B1 to B3.
If it's smaller than the sum, I need to add a constant($A$2) to D1
while reducing B1. Then D2=if(C2-SUM(B2:B4)C4,C2-B2,C2-B2+$A$2), and
so on.
But, the problem is that the $A$1=3 is NOT a constant number!!! For
different products, this A1 is different. Perhaps it's 6 sometimes, so
the D1 changes to "if(C1-SUM(B1:B6)C6,C1-B1,C1-B1+$A$2)". Since
there're hundreds of cases to deal with, certainly I don't want to
change the formulas again and again.

To be explicit, I need to change the formulas in column D, according
to the value of $A$1. If
$A$1=3,D1="if(C1-SUM(B1:B3)C3,C1-B1,C1-B1+$A$2)",
D2="if(C2-SUM(B2:B4)C4,C2-B2,C2-B2+$A$2)"...
When $A$1=4, D1="if(C1-SUM(B1:B4)C4,C1-B1,C1-B1+$A$2)",
D2="if(C2-SUM(B2:B5)C5,C2-B2,C2-B2+$A$2)"...

To be more simplified, I need to firstly get the value of $A$1, for
example $A$1=6, then I cite this value to be a mark for another cell,
for instance, $B$6. But it's NOT always 6, it's changing according to
different $A$1. However, I tried to adopt something like $B$($A$1),
but it's not possible.

Would you please help me to resolve the trouble? Thank you very much.

zh.y