Change The ROW / COLUMN from a formula

[JhonsonTan]

C8 = "20" , K8 = "$1000" , K22 = "$2000"
if c3 = "=c8" , THEN c2 = k8 = "$1000"
if c3 = "=c22" , then c2 = k22 = "$2000"
so if i just need to change c3 value , then c2 will follow
is this possible?
i want the formula in C2 cell
thank you

 

[JhonsonTan]

You would need VBA to do that. This may help you:

Color Functions In Excel

thanks for the link.. but im not good at vba.

next question?
C3 = "=c#" , d1 = "=sum(c(#+1):c1000)"
ex. c3 = c8 then d1 = sum(c9:c1000)

 

[Andrew Poulsom]

The formula would be:

=SUM(INDEX(C4:C1000,MATCH(C3,C4:C1000,FALSE)+1):C1000)

 

[JhonsonTan]

The formula would be:

=SUM(INDEX(C4:C1000,MATCH(C3,C4:C1000,FALSE)+1):C1000)

where do you learn this magic? ure awesome. thanks for the help!
but is there another way beside using match?
i got a problem when there is same value between c4:c1000

n also another question please
c3 = "c#" , c2 = move to 1column from c# = (d#)

 

[Andrew Poulsom]

What do you want to do with duplicate values?

A formula can only return a result to the cell that contains it. It can't affect other cells.

 

[JhonsonTan]

What do you want to do with duplicate values?

A formula can only return a result to the cell that contains it. It can't affect other cells.

sorry what do you mean by duplicate value? i dont follow

=SUM(INDEX(C4:C1000,MATCH(C3,C4:C1000,FALSE)+1):C1000)
this can solve
C3 = "=c#" , d1 = "=sum(c(#+1):c1000)"
ex. c3 = c8 then d1 = sum(c9:c1000)

but when there is 2 cell with the same exact value, it doesnt work
ex. c3 = c20 , c8 = 40 , c20 = 40
=SUM(INDEX(C4:C1000,MATCH(C3,C4:C1000,FALSE)+1):C1000) -> this would result =sum(c9:c1000)
instead of that i want =sum(c21:c1000)

n this is one is another case, not connected to the other one
c3 = "c#" , c2 = move to 1column from c# = (d#)

 

[Andrew Poulsom]

In your example the value 40 is duplicated in the range C4:C1000. To use the last occurrence of the matching number:

=SUM(INDEX(C4:C1000,MAX(INDEX((C4:C1000=C3)*(ROW(C4:C1000)-ROW(C4)+1),))+1):C1000)

I don't understand your second question. You can't move data with a formula.

 

[JhonsonTan]

In your example the value 40 is duplicated in the range C4:C1000. To use the last occurrence of the matching number:

=SUM(INDEX(C4:C1000,MAX(INDEX((C4:C1000=C3)*(ROW(C4:C1000)-ROW(C4)+1),))+1):C1000)

I don't understand your second question. You can't move data with a formula.

you're a **** good magician!

sorry, i mean get the data right next to it. ive been done it before using offset, but i forgot how to do it.
c3 = "c#" , c2 = value in (d#)
i want the c2 formula

 

[Andrew Poulsom]

You can adapt the formula in Post #8:

=INDEX(D4:D1000,MATCH(C3,C4:C1000,FALSE))

 

[JhonsonTan]

You can adapt the formula in Post #8:

=INDEX(D4:D1000,MATCH(C3,C4:C1000,FALSE))

wtf.. sorry i didnt realize it. ive come with solution
=OFFSET(INDIRECT(RIGHT(FORMULATEXT(D3), LEN(FORMULATEXT(D3))-1)),0,1,1,1)
but i think yours is better, since it can be drag to copy

sorry what do you mean by duplicate value? i dont follow

=SUM(INDEX(C4:C1000,MATCH(C3,C4:C1000,FALSE)+1):C1000)
this can solve
C3 = "=c#" , d1 = "=sum(c(#+1):c1000)"
ex. c3 = c8 then d1 = sum(c9:c1000)

but when there is 2 cell with the same exact value, it doesnt work
ex. c3 = c20 , c8 = 40 , c20 = 40
=SUM(INDEX(C4:C1000,MATCH(C3,C4:C1000,FALSE)+1):C1000) -> this would result =sum(c9:c1000)
instead of that i want =sum(c21:c1000)

n this is one is another case, not connected to the other one
c3 = "c#" , c2 = move to 1column from c# = (d#)

then i found a solution for this too.. instead of you using the last accourrence, i make it to found the exact match
=INDEX($DR4:$DR10000,MATCH(INDIRECT(RIGHT(FORMULATEXT(D3), LEN(FORMULATEXT(D3))-1)),D4:D10000,FALSE))/1000)
what do you think? ive tested it, but i dont know if i'd get error later

 

[Andrew Poulsom]

I don't know why you would need to use INDIRECT and FORMULATEXT. What's in D3?