Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Adam Kroger
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Comparing a list to a Calendar worksheet. PatrickL Excel Worksheet Functions 0 August 25th 05 04:21 PM
How do I use the TABLE worksheet function? Jo Excel Worksheet Functions 2 July 2nd 05 12:41 PM
Links to 2nd closed worksheet fail when using offset function ?? Jordan795 Excel Worksheet Functions 1 June 21st 05 01:43 AM
Automatically up date time in a cell Mark Excel Discussion (Misc queries) 5 May 12th 05 12:26 AM
The Help on the Excel Edate worksheet function contains an error:. Guido Excel Worksheet Functions 1 January 20th 05 02:42 PM


All times are GMT +1. The time now is 01:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"