I am trying to perform a simulation in Excel using two different
scenarios each stored in a separate worksheet in the same workbook.

To combine the scenarios which have a certain probability of occurrence
I use a third worksheet as follows.

RN: Either 0 (0.25) or 1 (0.75)

A: = IF(RN = 0,ScenarioA!Cell,0)

B: = IF(RN = 1,ScenarioB!Cell,0)

SUM: = SUM(A,B)

For some reason beyond my understanding Excel doesn't recognize the
cell reference in cell B, it calls me to update values manually each
time through a dialogue box and when I don't the cell displays #REF!
error. Cell A works fine.

I think this might have something to do with range names since the
referenced worksheets share common range names (I tried different
workbooks and the same thing happened) but I can't see how.