Excel Recalculate Specific Cell Random Numbers

[help2017]

Hi,

I'm having a problem with a specific issue and looking for help, i'm not a excel expert, so please explain in layman's terms.

Setup
I have a set of randbetween 1-100 in A1:A100, then in Column B1:B100 1 to 100 in sequential order (1,2,3,4,5,etc...), in Column C1:C100 a formula (A1*B1, A2*B2, etc...)
Having the sheet set in manual calc mode to keep the random number for re-generating.

Issue:
If i don't like the random number in A6 and would like to select that specific cell and recalculate that specific cell only and it dependencies (C6), how is this done without effecting the other randbetween calculations? Keep in mind the actual sheet is much more complicated than listed above with multiple layers of dependent cells.

Any help is appreciated.

 

[JazzSP8]

You main problem is that RANDBETWEEN is whats known as a Volatile function. It'll recalculate every time Excel performs a calculation exercise.

If you only need the number to be random once then once RANDBETWEEN has given you your figures you can Copy and Paste them as Values?

Other than that you're going to be needing a rethink it sounds like

 

[Scott T]

If you put a cell in edit mode (press F2) then hit enter the cell will recalculate and you will get a new random number. The problem is column C will not recalculate. So you could F2+enter in column A and then column C or use code to do it.

In Excel press ALT+F11 to open the VBA editor
Select insert from the menu and select module

past this code into the new module

Sub recalcell()
ActiveCell.Calculate
Cells(ActiveCell.Row, 3).Calculate
End Sub

Back in Excel press ALT+F8 to open the macro box. Select the macro and click on options.
You can then assign a short cut to the macro.

Select the cell you want to change and trigger the macro using the short cut you assigned it. The code will recalculate the active cell and the cell in column C of the same row.

 

[help2017]

You main problem is that RANDBETWEEN is whats known as a Volatile function. It'll recalculate every time Excel performs a calculation exercise.

If you only need the number to be random once then once RANDBETWEEN has given you your figures you can Copy and Paste them as Values?

Other than that you're going to be needing a rethink it sounds like

Thanks JazzSP8, was afraid of that answer. Appreciate the help.

 

[help2017]

If you put a cell in edit mode (press F2) then hit enter the cell will recalculate and you will get a new random number. The problem is column C will not recalculate. So you could F2+enter in column A and then column C or use code to do it.

In Excel press ALT+F11 to open the VBA editor
Select insert from the menu and select module

past this code into the new module

Sub recalcell()
ActiveCell.Calculate
Cells(ActiveCell.Row, 3).Calculate
End Sub

Back in Excel press ALT+F8 to open the macro box. Select the macro and click on options.
You can then assign a short cut to the macro.

Select the cell you want to change and trigger the macro using the short cut you assigned it. The code will recalculate the active cell and the cell in column C of the same row.

Thanks Scott, unfortunately in the actual file the dependent cells are not in the same row, they vary in rows and columns. Starting to look like, i might have to rethink the structure. Thanks again.