Array Formula Value Error

    Hi,


    I'm looking for some help on, what to me is a strange error. I have created this workbook utilizing a sumproduct array on the Summary page. The formula works perfectly on my workbook but if I send it to a coworker all of the formulas on the Summary page come back with a value error. I have created the workbook in 2013 version and my coworker is opening it in 2016. There are no external data references, all of the data is contained within this workbook on different tabs. I'm in North America and my coworker is in Europe, I doubt this makes any difference though.


    Here's where is gets weird. If my coworker sends me back the copy that they saved, the error is present. If I double click on any of the errors on the Summary page and then simply press enter the error goes away. If I go to column F on the Update Engagement tab and either double click on any date, or just click on the filter for column F and then Okay, the error goes away. One thing I do notice is that if I double click on the date in column F it eliminates the leading zero. If my coworker tries any of these steps the error does not resolve.


    I'm sure it's probably something simple but it has me stumped. Thank you in advance for taking the time to look at this.

    I also get errors on the summary sheet and the problem is the dates in column F being in MDY format, i.e. US format and not being recognised as dates, in Europe anyway.


    If I convert the dates using Data>Text to columns with the Column data format MDY on the third step then the formulas work.


    Your colleague should be able to do the same.


    Mind you, I was under the impression that provided the dates were valid the format wouldn't matter and would actually change based on locale, i.e. if the workbook was opened in the US the dates would appear in MDY format and if opened in Europe they would be in DMY.

    Boo!:yikes:

    Hi norie,


    Thank you. I too have narrowed it down to the date format. The issue I've seen with column F is that neither I or my colleague are able to change the format of the column by right clicking. No matter what format I chose it doesn't change the data in column F. I have to double click on a cell before I can change the content of that cell. I thought if you set the date so it has the * in front of it the date should automatically be changed in each region?


    This is meant to be a workbook where you can run reports from sources such as LinkedIn and paste them into the data sheets so the Summary page can gather the required data. I need to be able to make this as easy to use as possible so people in the UK and EU won't have to worry about formatting.

    Changing the format isn't the problem, it's the value that's the problem.


    When I opened the workbook the dates aren't being recognised as dates, I assume it's the same for your colleague.


    I converted them to 'real' dates by selecting column F, going to Date>Text to columns... and selecting MDY for the Column data format on the 3rd step.


    As for preceding the format with a * - the dates in your workbook aren't formatted like that.


    How are you getting the data from LinkedIn et al?

    Boo!:yikes:

    Love 1

    Hello,


    Norie is right ...


    Since your file is using a string showing a US date format ... it is not a number but a string which needs to be converted ...


    see your attached test file


    Hope this will help

    :)

    Glad you could fix your problem :)


    Thanks for your Thanks ...AND for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login