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

 

[Andrew Poulsom]

Here's a comparison of VLOOKUP and INDEX/MATCH:

Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel

For your formula in B1 copied across:

=INDEX($A:$A,COLUMNS($B1:B1))

 

[JhonsonTan]

DATA
| A | B | C |
1|--A--|-AA--|--1--|
2|-----|-----|--2--|
3|-----|-----|--3--|
4|--B--|-BB--|--4--|
5|--C--|-CC--|--5--|
6|-----|-----|--6--|
7|--D--|-DD--|--7--|
8|-----|-----|--8--|
9|-----|-----|--9--|




IF D1 = A1 , RESULT
| D | E | F | G |
1|--A--|--B--|--C--|--D--|
2|--AA-|--BB-|--CC-|--DD-|
3|--1--|--4--|--5--|--7--|
4|--2--|-----|--6--|--8--|
5|--3--|-----|-----|--9--|
6|-----|-----|-----|-----|


IF D1 = A4 , RESULT
| D | E | F |
1|--B--|--C--|--D--|
2|--BB-|--CC-|--DD-|
3|--4--|--5--|--7--|
4|-----|--6--|--8--|
5|-----|-----|--9--|
6|-----|-----|-----|


IF D1 = A5
| D | E |
1|--C--|--D--|
2|--CC-|--DD-|
3|--5--|--7--|
4|--6--|--8--|
5|-----|--9--|
6|-----|-----|


i want the formula for e1,e2,e3,e4,e5,e6
that can be drag to right

 

[JhonsonTan]

Hi andrew.. can u help me solve this please

B1 = sum(A#:A*)
B2 = count(A#:A*)

if B1 = sum(A1:A5) then B2 = count(A1:A5)
if B1 = sum(A2:A5) then B2 = count(A2:A5)
if B1 = sum(A3:A7) then B2 = count(A3:A7)
something like that
i want b2 to count the cell that b1 provide

 

[Andrew Poulsom]

Are you just asking these questions out of curiosity or do they apply to a real scenario?

I would put the start and stop values in a couple of cells and use INDEX to build the relevant ranges, eg:

=SUM(INDEX(A:A,C1):INDEX(A:A,C2))

 

[JhonsonTan]

Are you just asking these questions out of curiosity or do they apply to a real scenario?

I would put the start and stop values in a couple of cells and use INDEX to build the relevant ranges, eg:

=SUM(INDEX(A:A,C1):INDEX(A:A,C2))

im learning and im working on a worksheet. im using this worksheet daily, i want to make connection between cell so i just need to input less data.
in this case, i need to sum cost of some package. Beside to sum the cost, i also want to count how many is the package. that it
so, is there is a way which i dont need to use c1 and c2?
anyway thanks for your response again.

 

[Andrew Poulsom]

I suppose you could extract the range reference from the formula in B1 using FORMULATEXT, but I don't see the point in making things that complicated.

 

[JhonsonTan]

So what do you suggest?

 

[Andrew Poulsom]

What I have already suggested.

 

[JhonsonTan]

Okay.. thanks for the help

 

[JhonsonTan]

What I have already suggested.

andrew.. it doesnt work.
formulatext cant get the range cell.
do you have any suggestion?