#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.
0
Answers
Can you provide a screenshot of exactly what you are seeing?
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!
SMARTSHEET PARTNER & CONSULTANT / EXPERT
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
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?
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.
@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])
SMARTSHEET PARTNER & CONSULTANT / EXPERT
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.
Sorry for the delay. I had to delete quite a bit off the copy.
Dashboard with #INVALID REF error on widget.
https://app.smartsheet.com/b/publish?EQBCT=c3b5c9e8ee374aa39434265b77f443ef
Source sheet has Sheet Summary populating expected results in the Current Reporting Month.