If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Is there a worksheet function that will...
Is there a worksheet function that will copy (or return) the value of
another cell (not its formula), with the result being stable, even though the orriginal cell was volitile? =VALUE(B3) will return the data but it remains volitile. I want to have a cell with an IF statement that when true will show what the current RAND() number is as a number only. ie IF(A1="Y",COPYasVALUE($B$3),"") of course COPYasVALUE is not really an existing function in excel. The end result I am trying to accomplish is to fill a range of cells with a non-volitile randomly determined number only once, when a condition is met. Here is one attempt of mine to accomplish this, it didn't work, and refers to itself as well. =IF(ISBLANK(V4),IF(U4="Y",ROUNDUP(RAND()*6+1,0),"" ),"") ALTERNATELY: Can you cause a portion of a spreadsheet to be calculated only on demand? Devide the spreadsheet into 8 sections, and certain of the cells within the section would calculated only when a button would push, and not affected when the button for another section is pushed. Sorry for the multiple directions of my questions, I am tryign to approach the problem from as many angles I can to find a solution. |
#2
|
|||
|
|||
Is there a worksheet function that will...
On Sun, 20 Nov 2005 11:41:15 -0600, "Adam Kroger"
wrote: Is there a worksheet function that will copy (or return) the value of another cell (not its formula), with the result being stable, even though the orriginal cell was volitile? =VALUE(B3) will return the data but it remains volitile. I want to have a cell with an IF statement that when true will show what the current RAND() number is as a number only. ie IF(A1="Y",COPYasVALUE($B$3),"") of course COPYasVALUE is not really an existing function in excel. The end result I am trying to accomplish is to fill a range of cells with a non-volitile randomly determined number only once, when a condition is met. Here is one attempt of mine to accomplish this, it didn't work, and refers to itself as well. =IF(ISBLANK(V4),IF(U4="Y",ROUNDUP(RAND()*6+1,0)," "),"") ALTERNATELY: Can you cause a portion of a spreadsheet to be calculated only on demand? Devide the spreadsheet into 8 sections, and certain of the cells within the section would calculated only when a button would push, and not affected when the button for another section is pushed. Sorry for the multiple directions of my questions, I am tryign to approach the problem from as many angles I can to find a solution. It sounds as if what you require something that will, *on command*, copy to column A a random set of numbers from column B, and then not change those numbers until the command is called again. You cannot do that with a formula, but you can with a "macro". One way to get started with the process is to use the Record Macro wizard and then make modifications as needed. For example, let us say your range of randomly generated numbers, (using the formula =RAND()) is in Z2:Z30 and you want your static numbers to be in A2:A30. Tools/Macro/Record New Macro/OK Select Z2:Z30 Edit/Copy Select A2 Edit/Paste Special Values Tools/Macro/Stop Recording If you then select Tools/Macro/Macros and RUN the just recorded macro, you've done what you request (I think). However, the Macro itself is "messy". Select Tools/Macro/Macros Select the macro you just recorded and "EDIT" A window will open in the VB Editor and it has a lot of extraneous stuff there. On my recording it looks like: ======================== Option Explicit Sub Macro1() ' ' Macro1 Macro ' Macro recorded 11/20/2005 by Ron ' ' Range("Z2:Z30").Select Selection.Copy ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 1 Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub ============================= This can be simplified and renamed: ============================ Option Explicit Sub CopyAsValue() ' ' Macro1 Macro ' Macro recorded 11/20/2005 by Ron ' Range("Z2:Z30").Copy Range("A2").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False End Sub ============================== Now, whenever you want to copy the Values that are in Z2:Z30 to A2:A30, all you have to do is RUN that macro. You can also attach it to a toolbar button; set up a shortcut key to access it; etc. --ron |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Attaching Code | DS | General Discussion | 2 | August 22nd, 2005 11:21 PM |
Function to return the current Worksheet name? | Steve | Worksheet Functions | 6 | June 29th, 2005 04:36 AM |
Date & Time | mully | New Users | 4 | May 23rd, 2005 11:56 AM |
What Excel function returns the current worksheet name? | jr | Worksheet Functions | 2 | September 27th, 2004 02:53 PM |
Returning multiple values from user worksheet function | Michael Hlavinka | Worksheet Functions | 2 | February 5th, 2004 08:09 PM |