Problems with Reports failing to format numbers to match source sheet.

Options

Is anyone having problems with numbers in a report failing to format to match the source sheet?

I'm attaching 2 screen shots. One where the report is showing additional decimals when source sheet is rounded to a whole number. A second screen shot shows the report failing to format the number in currency ($) when clearly the source sheet is formatted that way.

Any help or insight would be greatly appreciated

Best Answer

  • Brian_Richardson
    Brian_Richardson Overachievers
    Answer ✓
    Options

    I don’t think it’s Control Center- CC just creates the cell links it’s not reaching in and messing with format.

    I don’t mean to beat the same drum but the report columns you’re showing look like they are coming from your summary sheet, not the metadata sheet. So I’d make sure the column in your CC summary sheet is also formatted.

    If not that, the only other clue that I can see is that there’s a mix of text and numbers in that reported column. Maybe the report format is seeing text and defaulting the format for some reason to accommodate both? Does it happen in a column that’s pure numbers? Perhaps it would be better just to have null in the cell instead of text if there’s no data yet.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Answers

  • Brian_Richardson
    Brian_Richardson Overachievers
    Options

    You need to format the Helper column, not the source column. Because you're cell linking data into the Helper Column and showing the Helper Column on the report, the report will take the format of the Helper Column.

    Cell linked data comes in and takes the format of the destination column, it doesn't retain the format of the source.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Mike Ferringer
    Mike Ferringer ✭✭✭✭✭
    Options

    Thank you Brian_Richardson for the quick reply. This was the first thing that I checked and confirmed that both the original source sheet (Budget Tracker) and the Helper column in the Metadata sheet that it is linked to were formatted correctly (screen shot attached). The Report is pulling the data from the Value Helper column in the Metadata sheet which is what I showed in my original attachments.

    Note: These Reports are being updated through a Global Update (Report Update) in Control Center. Not sure if that has an impact but worth noting. I have tried it both with the "Update Report Sources" box checked and not checked - no difference.

  • Brian_Richardson
    Brian_Richardson Overachievers
    Answer ✓
    Options

    I don’t think it’s Control Center- CC just creates the cell links it’s not reaching in and messing with format.

    I don’t mean to beat the same drum but the report columns you’re showing look like they are coming from your summary sheet, not the metadata sheet. So I’d make sure the column in your CC summary sheet is also formatted.

    If not that, the only other clue that I can see is that there’s a mix of text and numbers in that reported column. Maybe the report format is seeing text and defaulting the format for some reason to accommodate both? Does it happen in a column that’s pure numbers? Perhaps it would be better just to have null in the cell instead of text if there’s no data yet.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

  • Mike Ferringer
    Mike Ferringer ✭✭✭✭✭
    Options

    Hi Brian. Thanks for your help on this. You were spot on with your comments. It was an issue with the summary sheet. I formatted those columns resulting in the report being properly formatted as well.