#INVALID REF for Widget

#INVALID REF for Widget

Hi Community. I'm hoping someone can help me out. I created a task tracking sheet. A general rule is that our reporting month is always two month behind today. In my sheet summary I have a formula using index match, which works perfectly to my expected results. I am using a date reference table so I can see the month name instead date format.

The issue I am having is whenever use the sheet summary to create a metric widget, it states it is an invalid reference. Anyone know what gives? I would like my dashboard to be as dynamic as possible not require a lot of maintenance.

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Can you provide a screenshot of exactly what you are seeing?

  • Andrée StaråAndrée Starå ✭✭✭✭✭
    edited 07/31/20

    Hi @Jamie Wisegerber

    Strange!

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • For whatever reason my screenshots must be too big to directly embed in the message so I've put them on a Word doc. Any ideas as why the sheet summary works but not the widget?


  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Can you attach the word doc?

  • @Paul Newcome apparently today is not my today. I can't even get the attachment to upload! <insert facepalm here>

    In my task tracking sheet I am using the sheet summary for sole purpose of creating roll up reports for chart or metric widgets. It's probably important to note that the rollup up report displays the same #INVALID REF error.

    My sheet summary uses the following:

    =INDEX({Date Tables Range 4}, MATCH(MONTH(TODAY()) - 2, {Date Tables Range 3}))

    The Date Tables reference is just a helper sheet with the number of months and their respective names. The formula results as May, which I expected.

    Whenever I run the sheet rollup report or use the sheet summary option for selecting data to populate a widget is where I am running into the error. I have compared my formula to all the others that I have in the summary and this is only one I am using INDEX MATCH.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Jamie Wisegerber

    I'd be happy to take a quick look.

    Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

Sign In or Register to comment.