#SPILL! From a Referenced Merged Cell.

%3CLINGO-SUB%20id%3D%22lingo-sub-1122816%22%20slang%3D%22en-US%22%3E%23SPILL!%20From%20a%20Referenced%20Merged%20Cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1122816%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20face%3D%22ms%20pmincho%2Chiragino%20mincho%20pron%22%3EHi%20everyone%2C%20I%20have%20a%20spreadsheet%20that%20I'm%20trying%20to%20build%20for%20my%20work%20flow%20to%20enable%20track%20my%20daily%20sales%20activities%26nbsp%3Band%20simultaneously%20compute%20my%20percentage%20earnings%20per%20customer.%20For%20me%20to%20achieve%20this%20I%20need%20to%20merge%20certain%20number%20of%20rows%20and%20work%20with%20two%20separate%26nbsp%3Bworksheets%2C%20thus%20I%20can%20reference%20dates%2C%20customer%20names%20for%20the%20input%20sales%20sheet%20to%20the%20salary%20sheet%20but%20immediately%20I%20referenced%20it%2C%20there%20was%20a%20%23SPILL!%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22ms%20pmincho%2Chiragino%20mincho%20pron%22%3E%26nbsp%3B%20Please%20how%20do%20I%20eliminate%20this%20error%20because%20for%20my%202019%20worksheet%2C%20this%20did%20not%20appear.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1122816%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1123744%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20From%20a%20Referenced%20Merged%20Cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1123744%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%2C%20the%26nbsp%3B%40before%20the%20formula%20worked.%20No%20I'm%20ready%20to%20work%20on.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1123318%22%20slang%3D%22en-US%22%3ERe%3A%20%23SPILL!%20From%20a%20Referenced%20Merged%20Cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1123318%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F528567%22%20target%3D%22_blank%22%3E%40ituryu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20means%20you%20are%20on%20dynamic%20array%20version%20of%20Excel%20and%20your%20formula%20returns%20an%20array%20for%20which%20not%20enough%20space.%202019%20returns%20only%20first%20element%20of%20such%20array.%20Better%20to%20rework%20the%20formula%2C%20but%20you%20may%20try%20to%20put%20in%20front%20of%20it%26nbsp%3B%40%20sign.%20For%20example%2C%20if%20your%20formula%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(...)%0A%0Aconvert%20it%20to%0A%0A%3D%40INDEX(...)%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi everyone, I have a spreadsheet that I'm trying to build for my work flow to enable track my daily sales activities and simultaneously compute my percentage earnings per customer. For me to achieve this I need to merge certain number of rows and work with two separate worksheets, thus I can reference dates, customer names for the input sales sheet to the salary sheet but immediately I referenced it, there was a #SPILL!

  Please how do I eliminate this error because for my 2019 worksheet, this did not appear.

12 Replies
Highlighted

@ituryu 

That means you are on dynamic array version of Excel and your formula returns an array for which not enough space. 2019 returns only first element of such array. Better to rework the formula, but you may try to put in front of it @ sign. For example, if your formula 

=INDEX(...)

convert it to

=@INDEX(...)
Highlighted

@Sergei Baklan 

Thank you so much, the @before the formula worked. Now I'm ready to work on. 

 

Cheers.

Highlighted

@ituryu , you are welcome

Highlighted

@Sergei Baklan I am trying to populate an empty merged cell with a merged text cell from one sheet to another. It is now displaying #Value! instead of the name I am trying to reference. For reference, the formula I am using is =@'Client Info Sheet'!B6:D6. 

Highlighted

@Rebecca_L4 

Did you try ='Client Info Sheet'!B6 ?

Highlighted

@Sergei Baklan THANK YOU. Yes, that worked perfectly. Now I have another question that maybe you can help me with. I have a cell on my spreadsheet that should show someone’s age (it should show either current age or age at death, whichever applies). I am trying to find a formula that would calculate these scenarios. I believe it should be an if/then type statement, because whatever the answer, it should populate this one cell. My spreadsheet currently has one cell with the today’s date (DATE), one for the date of birth (DOB) and a cell which would only be populated if the person is deceased (DOD). So, theoretically the age would be found using combinations of those three cells. 

Highlighted

@Rebecca_L4 

That could be

=YEARFRAC(DOB,IF(DOD="",TODAY(),DOD))

and apply desired number format to the result.

Highlighted

@Sergei Baklan Thank you. I'm sorry for the incessant questions, but here's another: I formatted the cells for date, but I still have to type them in with slashes (ex. 10/15/2020), and I don't understand why. I am wondering if there is a custom one that will work like the phone number one. For phone numbers, I just have to type in 10 numbers and it automatically puts them in the correct form of (123) 456-7890. 

Highlighted

@Rebecca_L4 

Sure, Excel will format dates for you just like it formats phone numbers.  The number format

[$-en-US]mm/dd/yyyy

will do it.  The catch is that you need to enter

44119

(the number of days elapsed since the start of the 1900s)

Highlighted

@Peter Bartholomew yes, that is exactly what's going on here. I don't know why Microsoft did it this way. The average consumer does not think in terms of "days since 1900." I'll just continue to type the dates in long form. Thanks!

Highlighted

@Rebecca_L4 

Datetime is continuous and sequential. In all cultures it starts from one day which people agree to consider as beginning of their history, after that only representation of the dates counted from that very beginning. Same in software programs, the only difference that's the start date which is chosen. Excel for Windows takes Jan 01, 1900. 

 

Phone numbers are random and have no links with each other, you may take them as it is and apply any format you wish.

 

You may imitate date format for numbers like

image.png

but it'll be the number as in formula bar which can't be calculated as a date and can't be validated as a date.

Highlighted

@Rebecca_L4 

Ah, that would be because Excel's dates are designed to support calculation. 

 

Your 'long form' comprises the number of times the moon has circuited the Earth, with part periods represented by the number of times the Earth has rotated on its axis and finishing with the number of times the Earth has circuited the Sun since some event deemed to be of importance.  BTW, why do you place the smallest interval in the middle?

 

You see the problem?