Reference cell value/result as formula

breadzeppelin

New Member
Joined
Jun 11, 2020
Messages
7
Office Version
  1. 365
Platform
  1. 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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

breadzeppelin

New Member
Joined
Jun 11, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hold fire - I think I may have inadvertently asked something very similar before on this! My apologies! I will double check that first...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,253
Messages
5,600,544
Members
414,387
Latest member
Vincent88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top