The context :

Each time i need a cell reference from my other sheet, I need others cell reference from the same line but from different column.

I would like to automate those adding reference values depending on the first reference i add manually.

I have a sheet named alpha and a sheet named beta

I have a cell A1 in alpha that refers to beta A1

I have this referred cell formula in alpha A1 to have the value from beta A1 =beta!A1

Objectif :

I would like to have in alpha B2 the cell automatically filled-in by the value from beta A7
the equivalence of this refered cell formula
=beta!A7 Because the number seven will never change, only the column can change it could be:
=beta!AC1
so I will need =beta!AC7 automatically populated.

The Goal :

The goal is to extract from the formula in alpha A1 all characters except number(s) by a formula (like SEARCH, LEN, from FORMULATEXT) to obtain
=beta!A
with this result, I will concatenate with a cell that has the value "7". to obtain the formula for the cell reference.

If anyone can give me this formula clue or a better way to reach my goal would be good I tried the regex below but it removed the punctuation
=REGEXREPLACE(FORMULATEXT(A1),"[^[:alpha:]]", "")&C1 where C1 is the value "7" But i have all the need without the exclamation mark "!"

=betaA7 instead of
=beta!A7 Thank you

CodePudding user response：

this is what you want

Use this to

A better way

See in Beta!A1 we have a value of Im beta A1
And in Beta!A7 we have a value of Im beta A7

And in alpha!A1 we hane the formula =beta!A1

To get the value of beta!A7 using the formula in alpha!A1 as an input we use this formula in beta!B1

=INDIRECT(REGEXREPLACE(SUBSTITUTE(FORMULATEXT(A1),"=",""), REGEXEXTRACT(SUBSTITUTE(FORMULATEXT(A1),"=",""), "[0-9] "), "")&$C1)

Explanation

Even more simpler approach

Use this formula directly

=OFFSET(beta!A1,C1-1,0)