turn a spreadsheet calculation into a function

[joe]

How can I obtain in spreadsheet #1 the results of a calculation
defined in spreadsheet #2 for a complete series of values defined as a
series in spreadshet #1?
In essence how can I build my own functions by defining them in a
spreadsheet?
Thanks, pepi
Direct answers to (e-mail address removed) also appreciated.

 

[David McRitchie]

Hi Joe, (posted to excel.programming)

sheet2!A1: 100
sheet2!A2: =3*A1+4

Sheet1!A1: =Sheet2!A1
sheet1:A2: =Sheet2!A1

you can select your cell on sheet1 type in = sign then select
the cell on A2 then return to sheet1

Part 2 is either answered above, since it sounded like you were
trying to expand on a description of part 1 or it's going to be a lot
more than you bargained for.

However if you want to see some examples of User Defined Functions
see http://www.mvps.org/dmcritchie/excel/formula.htm

 

[David McRitchie]

Hi Joe,
There is hardly such a thing as etc in programming if you don't know
where to stop. Things must be clearly defined as to exactly what you
you want to do. A function can only return a value see Chip Pearson's
Difference between a Macro and a Function
http://www.cpearson.com/excel/differen.htm

By doubleclicking on the fill handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm
you can copy down from the active cell as long as there is content
to the left. -- If it is impossible for anything to be on the left because
you are in Column A, it will check the content to the right (col B).

The following is a macro that will copy the formula from Sheet2 cell B1 to the
current sheet cell B1, and then using a macro Tom Ogilvy posted yesterday
simulate the use of doubleclicking the fillhandle of cell B1 on the current
sheet. Tom's macro started in row 2 this starts in row 1 per your
instructions.

Sub joestest()
Cells(1, 2).Formula = Worksheets("sheet2").Range("b1").Formula
'modified Tom Ogilvy's code from
' http://google.com/[email protected]
Dim rng As Range
Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
rng.Offset(0, 1).Formula = Cells(1, 2).Formula
End Sub



----- Email Message -----

Almost there but not quite:
what I need on sheet1 is to be able to set up a series A1, B1, C1 etc and
get A2, B2, C2 etc with the functional relationship defined in sheet2 by A1
and A2. The reason I can't use the A2 definition directly (as it would be
possible with a simple definition like in your example) is that it's too
complicated and takes many lines and columns to complete.
Thanks, joe
PS I believe your definition
sheet1:A2: =Sheet2!A1
was meant to be
sheet1:A2: =Sheet2!A2

yes that is what I meant