Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing a list to a Calendar worksheet. | Excel Worksheet Functions | |||
How do I use the TABLE worksheet function? | Excel Worksheet Functions | |||
Links to 2nd closed worksheet fail when using offset function ?? | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
The Help on the Excel Edate worksheet function contains an error:. | Excel Worksheet Functions |