1

I have two excel sheets. The first contains a formula for calculation with one input cell (A1), and one output cell (B1). The formula for B1 could be B1 = A1 * 3 (example).

The second sheet contains various values in column A: A1 = 4; A2 = 9; A3 = 5 ... In corresponding column B of this sheet I'd like to get the result of B1 (first sheet) = A1 (second sheet) * 3 for each A (second sheet) input value.

Basically I'd like to treat the first sheet as a function, where A1 is the argument and B1 the result that is passed back to the second sheet's B column.

Sheet 2

A1 4      B1 12 (result from sheet 1)  
A2 9      B2 27 (result from sheet 1)

...

Is it possible without macros?

Kara
  • 6,115
  • 16
  • 50
  • 57
Cosmo
  • 369
  • 1
  • 7
  • 16

5 Answers5

1

I don't think so ..... If in B1 Sheet1 you have 3*A1

If you try this in Sheet2 B1

`=SUBSTITUTE(Sheet1!$B$1,"A1",A1)`

it will give 3*4, and Sheet2 B2 will be 3*9etc

But I don't see how you could coerce this to a numberic calculation with formulae without possibly some heavy duty formula string parsing to separate numbers from operators (which is unlikley to flex as desired if you change the entry in B1 Sheet 1)

[Update 2: but fwiw I have done it with a named range]

I used this range name

RngTest

=EVALUATE(3*INDIRECT("rc[-1]",FALSE))

This is a global range name so it will work on any sheet, more powerful than my prior OFFSET effort. It multiplies the cell to the immediate left by 3

so entering =RngTest in B1:B3 (and then in this new example C1:C3 as well) gives the output you want enter image description here

brettdj
  • 54,857
  • 16
  • 114
  • 177
1

This is built into Excel. In version 2003, use the Data, Table menu.
You can find many examples on the net. Here is one.
You can create such tables with either 1 or 2 entries (parameters).

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • To add to this, see here: http://stackoverflow.com/questions/4640336/using-a-sheet-in-an-excel-user-defined-function/4640866#4640866 – jtolle Oct 04 '11 at 14:50
0

Is it possible without macros?

Yes!

You can now use =LAMBDA for this.

Define your function using the name manager, then reference it in your second sheet's formula.

See syntax at Introducing the LAMBDA function.

For more information about how to use the LAMBDA function, see the support documentation.

ed2
  • 1,457
  • 1
  • 9
  • 26
0

I think you want to use this in your sheet two column.

Sheet1!B1 * Sheet2!A1
Jesse Seger
  • 951
  • 1
  • 13
  • 31
  • This is actually not what I want. I would like to treat Sheet1 as a function (method) to return values based on arguments. – Cosmo Oct 04 '11 at 11:07
  • In the first paragraph you say that B1 = A1*3. In the second paragraph you say B1 = Sheet2!A1 *3. Which is it? – Jesse Seger Oct 04 '11 at 11:22
  • I think he wants to be able to write a formula "template" in Sheet1!B1. That template should then be adjusted to the appropriate location on Sheet2. – Jean-François Corbett Oct 04 '11 at 13:09
0

Entirely without VBA: expect lots of pain, I won't go there. But...

To substantially reduce the amount of pain, you could actually use this one tiny VBA user-defined function (not technically a "macro"), basically just a wrapper to make VBA's Evaluate function available in worksheet formulas:

Function eval(myFormula As String)
    eval = Application.Evaluate(myFormula)
End Function

You could then use it like this in cell B1 on sheet 2:

=eval(SUBSTITUTE(Sheet1!$B$1,"A1","A"&ROW()))

Note that this requires Sheet 1 cell B1 to contain A1*3 and not =A1*3, i.e. no equal sign. Maybe with a bit more fiddling around, it can be made to work even with the = sign there...

EDIT: Actually, if you format Sheet 1 cell B1 as Text before typing in the formula, this will work even if your formula starts with a =.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188