Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
turn a spreadsheet calculation into a function
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 also appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
turn a spreadsheet calculation into a function
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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "joe" wrote in message m... 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 also appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
turn a spreadsheet calculation into a function
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/groups?threadm=eZv...GP15.phx.g bl 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 --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm ----- 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 "David McRitchie" wrote in message ... 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 "joe" wrote in message m... 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 also appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Turn on/off calculation in worksheet | Excel Discussion (Misc queries) | |||
HOW DO I TURN SPREADSHEET HORIZANTAL? | Excel Worksheet Functions | |||
Turn off or not include lines in a calculation | Excel Worksheet Functions | |||
how do i turn off calculation of cells | Excel Discussion (Misc queries) | |||
How to turn a spreadsheet into a function? | Excel Worksheet Functions |