Forum Discussion
Named rows and spill error
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.
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).
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.
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?
The only was I know to fix this is to add "@" after each named range to take the respective location.
