Pulling Data into and from Merged Cell

Edited I over complicated the original.

The fact that BASE SCENARIO works is a matter of relative cell referencing. Try putting ='BASE SCENARIO'!E3:G3 in column E. You will get the same error.

The problem is with merged cells is that 'COMPARISON SCENARIO'!E3:G3 is actually returning an array of {"All Products",0,0} ... ie all the values in those 3 cells. Try clicking in the formula bar of O3 and hitting the F9 function key. You'll see the array.

We really discourage use of merged cells. They cause enormous havoc for formulas. They are the devil's spawn.

If you must stay with merged cells change

='COMPARISON SCENARIO'!E3:G3

to

='COMPARISON SCENARIO'!E3