#INVALID REF for Widget

Jamie Wisegerber

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.

Paul Newcome

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

Andrée Starå

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!

Jamie Wisegerber

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 Newcome

Can you attach the word doc?

Jamie Wisegerber

@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å

@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])