Quotation Number Generating

Copper Contributor

How do i ensure that the cell containing the randbetween function in E15 remain static and do not update when new values are entered elsewhere in the spreadsheet in Excel but only chnages when the column F15 is edited.

I have this function ="Quotation#: "&" "&UPPER(LEFT($F$15,5))&"-"&UNIQUE(RANDBETWEEN(185,1000))&"-"&TEXT(F10,"23") but it updates whenever i enter something in another cell.

2 Replies
To make sure that the RANDBETWEEN function in cell E15 remains static and only updates when the column F15 is edited, you can use a combination of Excel's built-in features.

Assuming you want the RANDBETWEEN value to remain unchanged unless you manually refresh it, you can utilize Excel's data calculation settings. Here's how you can do it:

Enable Manual Calculation:

Go to the "Formulas" tab in Excel.
Click on "Calculation Options."
Select "Manual."
Set Calculation Mode for Specific Cells:

Select cell F15 (or the entire column F if needed).
Right-click and choose "Calculate Sheet."
This will ensure that only the selected cells are recalculated when edited.
Now, the RANDBETWEEN function in cell E15 should only update when you manually recalculate the worksheet by pressing F9 or going to the "Formulas" tab and clicking "Calculate Now."
Thank You. It worked perfectly.