Excel referencing column and row in a formula in another cell

1
votes

Is it possible to create a formula for something like this:

  1. Say in cell A1 the formula is =B10
  2. In A2, I need a formula =B(10+5), or basically the row reference from cell A1 plus 5

So if A1=C23 then A2=C28 or if A1=D30 then A2=D35 and so on.

1
excel-formula
it is possible if that's what you wanna know – HellBaby
Well, I would like to know know how create that formula. If you could show me, I would really appreciate it. – guear

1 Answers

0
votes

here is one way to do it:

=INDIRECT((MID(FORMULATEXT(A1),FIND("=",FORMULATEXT(A1))+1,1)&(MID(FORMULATEXT(A1),FIND("=",FORMULATEXT(A1))+2,1000))+5))



UPDATE

here is the demo worksheet downloadable

SECOND UPDATE

here is the formula to replace the column reference: 

=INDIRECT((REPLACE(MID(FORMULATEXT(A1),FIND("=",FORMULATEXT(A1))+1,1),1,1,"D"))&(MID(FORMULATEXT(A1),FIND("=",FORMULATEXT(A1))+2,1000))+5)