Named rows and spill error

[RichNZ]

A common financial analyst model would define a name for a row (say row 1 is "Price").

You can then put a row with volume (row 2, or defined as "Volume")

In the past you could multiply the second row by the defined name to come up with an answer, in this case the Revenue.  For example D3 could have the formula:

=Price*D2  or   =Price*Volume

This was very useful as it made the logical flow very clear, and the relevance of the name functions.  

Now all that is coming up in various different permutation is #spill!, indicating it is trying to multiply the entire data set.  Why is this now different, and what can be done to get back the proper way?

[#2]

@RichNZ 

Since the introduction of dynamic arrays in Excel you will only need one formula. It will then spill in the adjacent cells. Just delete all the other formulas and let it spill.

[#3]

@Detlef Lewin Thanks for the help, though not quite sure what you mean with one formula. I am just using one formula (well I am testing two but neither work), see attached.

Even in relation to "let it spill", issue here is that by defining the whole row (as I don't know in advance how many columns / years I will have, so want to have flexibility to be able to add more) then it may spill out of workbook.

I guess I will just have to use the "@" in front of names to avoid dynamic arrays, even if this example of named rows should be reconsidered (and I am always worried that short cuts like this "@" will be dropped in future).

[#4]

@RichNZ 

Your defined names use up complete rows (=16384 cells).

If your formula is in column D there are only 16380 cells left to spill. Which causes the #SPILL! error.

The formula must be in column A.

[#5]

@Detlef Lewin 

So how do I code my formula to NOT get the spill error and access so my named range(row) only returns only one column D in this case - like it was before Dynamic Arrays?

[#6]

@nonAltoCG_36771765 

The only was I know to fix this is to add "@" after each named range to take the respective location.