Spill Error with named ranges on a table

[gillianoleary]

Hey, 

I have a table and when i enter =Hours*Rate it returns a #SPILL! but if i enter the same outside the table i get the result, i know i can enter =[@Hours]*[@Rate] in the table but i just want to know if will this affect the actual mos expert exam as in the tutorial they are getting the result with =Hours*Rate. 

[#2]

@gillianoleary 

It depends on concrete situation - are Hours and Rate named ranges or columns within the table or, as variant, named cells. In any case you cant use formula which return the spill within the table, only outside it and if there is enough space for the spill. Within the table second formula is correct, it returns result for the current row.

[#3]

@Sergei Baklan Hours and Rate are named ranges 

[#4]

@gillianoleary 

If named ranges when you shall use =Hours*Rate outside the table.

[#5]

@Sergei Baklan so you cannot use named ranges in the table?

was it the case that you could use named ranges in a table before and not get the spill error, I am just wondering as the tutorial used the named ranges in the table and it is not possible to do this now. 

thanks for your help

[#6]

@gillianoleary 

Nope, you can't use them within the tables for the Excel which support dynamic arrays. Perhaps tutorial is bit outdated and for previous Excel versions.

[#7]

@Sergei Baklan yeah it must be and thanks very much for your help, hopefully this change is recognised in the exam as I am using 365 rather than 2016 .

[#8]

@gillianoleary 

If I have understood your situation correctly,

= hours * rate

would only have worked in O2016 because of implicit intersection, which causes the formula to return a sequence of single-cell results.  Trying to enter a multi-cell (CSE) array formula would always have failed.  Now as the defaults have changed in MS365, you could achieve the previous result using

= @hours * @rate

Better, I believe, is to use the Table for input data, but then switching to dynamic arrays for calculation.

[#9]

In regards to :
Better, I believe, is to use the Table for input data, but then switching to dynamic arrays for calculation. Iam using two tables and try to paste data from one to other, get spill. Any help
would be appreciated.? Thanks in advance Don