breadzeppelin
New Member
- Joined
- Jun 11, 2020
- Messages
- 10
- Office Version
- 365
- Platform
- Windows
Hi Gurus!
First - apologies; I couldn't think of an appropriate title, but here's what I'm trying to do and why...
Basically I have a spreadsheet where a user has the freedom to enter any formula they desire. What I need to do is to replicate the 'structure' of that formula a number of times across the spreadsheet from the 'parent' formula (i.e. what they typed). For example:
1). if they entered:
=A1+B1 in cell A2, then I'd like cell the formula in cell G2 to calculate: =G1+H1
2). if they entered:
=A1+B1+D5 in cell A2, then I'd like cell the formula in cell G2 to calculate: =G1+H1+J5
and so on...
Now, sure - I could write a macro to simply copy the formula from range A2 to range G2 and this would work fine... However I'm trying to use a way that avoids VBA or custom functions if possible. I've managed to get the desired formula as a result in cell G2 (i.e. the result of cell G2 displays as: =G1+H1 for example, but the formula to generate this result is actually via the cell formula which is: =SUBSTITUTE(F48,G49,G47).
So my question is: Can I use a formula to use the cell result (the "formula" displayed) from the result of: =SUBSTITUTE(F48,G49,G47) ?
I took a look over using the INDIRECT function, but that doesn't seem to work with formulas as such. I'm 99% of the way there, however stumped with turning the text formula into a real working formula
Any help would be hugely appreciated!
Thanks!
Oz
First - apologies; I couldn't think of an appropriate title, but here's what I'm trying to do and why...
Basically I have a spreadsheet where a user has the freedom to enter any formula they desire. What I need to do is to replicate the 'structure' of that formula a number of times across the spreadsheet from the 'parent' formula (i.e. what they typed). For example:
1). if they entered:
=A1+B1 in cell A2, then I'd like cell the formula in cell G2 to calculate: =G1+H1
2). if they entered:
=A1+B1+D5 in cell A2, then I'd like cell the formula in cell G2 to calculate: =G1+H1+J5
and so on...
Now, sure - I could write a macro to simply copy the formula from range A2 to range G2 and this would work fine... However I'm trying to use a way that avoids VBA or custom functions if possible. I've managed to get the desired formula as a result in cell G2 (i.e. the result of cell G2 displays as: =G1+H1 for example, but the formula to generate this result is actually via the cell formula which is: =SUBSTITUTE(F48,G49,G47).
So my question is: Can I use a formula to use the cell result (the "formula" displayed) from the result of: =SUBSTITUTE(F48,G49,G47) ?
I took a look over using the INDIRECT function, but that doesn't seem to work with formulas as such. I'm 99% of the way there, however stumped with turning the text formula into a real working formula
Any help would be hugely appreciated!
Thanks!
Oz