CODEWITHSUNDEEP
excelexcel-formulavlookupexcel-indirect
user1818648
user1818648

Reputation: 11

EXCEL Formula in master sheet

I want to be able to enter a formula in a "master sheet". That formula when called will be executed on the sheet in which the formula was called from.

Example:

Sheet 1 (master sheet)
Col A    Col B
ABC      A1 
DEF      A2xA2
GHI      A3-A1 * .05
JKL      A4+A1

Sheet 2
Col A    Col B     Col C
5        DEF       
10       ABC
15       GHI
20       JKL

So what I want to happen is whatever I type into Col B on Sheet2 to executes the formulas on Sheet 1 (Master Sheet) relative to the corresponding columns on Sheet 2. So in the example above, Line 1 of Sheet 2 for Col C would be 25. I was trying a mixture of VLOOKUP and INDIRECT. It works fine if it is just the cell reference such as ABC on Sheet1. If I enter any of the others it bombs out. Since VLOOKUP works fine, is there anything that can "execute" the formula or evaluate the formula? Or how to get indirect to work when more than just a straight cell reference.

I am not interested in a VBA solution, I want to use straight excel functions.

Upvotes: 1

Views: 1700

Answers (1)

Mike Rodey
Mike Rodey

Reputation: 296

This is easy using VBA because you can create a UDF in a module that calls Excel's Evaluate function, which is unavailable from worksheets:

Function Eval(ByVal r As Range)
    Application.Volatile True
    Eval = Evaluate(r.Value)
End Function

Then if you have SUM($A$1:$A$10) as text in cell C1 for example, to execute that text as a formula in a different cell all you need to do is enter:

=Eval(C1)

If you really must avoid VBA the Evaluate function is available when defining names. So with the above example of having SUM($A$1:$A$10) in C1 you could define a new name called SumCol that refers to the following:

=Evaluate($C$1)

And then you can put SumCol into a cell:

=SumCol

But I imagine defining a bunch of names like this would become tedious.

Upvotes: 1

Related Questions