Chart Widget on dashboard

Paul Newcome

You will need to "pre-fill" some rows in the metrics sheet, but it is relatively straightforward. Here's a breakdown:

Insert a text/number column called "Number". You want to pre-fill this column starting with 1 in row 1 and moving on down until you have as many rows as you think you will need plus a buffer. If you anticipate 50 locations, I suggest pre-filling down to 75. If you anticipate 150 locations, pre-fill down to 175. Whatever number you feel comfortable that you will not exceed.

1

2

3

4

5

so on and so forth...

Then in the Primary Column you would use this column formula:

=INDEX(DISTINCT({Source Sheet Location Column}, Number@row)

Since I am not sure of the exact structure of your source sheet, I cannot say for sure which you will need, but from there you would use a COUNTIFS or SUMIFS with cross sheet references to get your totals by location.

Christiana Gkini

Hi Paul,

Unfortunately it doesn't work. I would really appreciate if you have time for a quick call to show you and maybe you could come up with the best advise for me in this case. Let me know if that would be possible.

Thank you!

Paul Newcome

@Christiana Gkini Which part is not working? Are you getting error messages or incorrect outputs? Are you able to provide screenshots of what is broken and the formulas used?

Christiana Gkini

@Paul Newcome This formula works only. However, this does not work for me as I have 110 different locations and I can't put every single location in different formulas for each different type of transaction.

Paul Newcome

You need to use this to populate the locations:

=INDEX(DISTINCT({Source Sheet Location Column}, Number@row)

Then in the COUNTIFS you would use a cell reference instead of "specific text".

Christiana Gkini

Apologies but I don't know how to use this formula. Not sure how exactly to do this. Is there any way you could help me?

Thank you

Paul Newcome

@Christiana Gkini You would put it in any cell within the column, right click on it, then (down near the bottom) select the option to convert it to a column formula.

Christiana Gkini

I just tried that but it still doesn't work.

Andrée Starå

Hi @Christiana Gkini

I hope you're well and safe!

Try something like this.

= INDEX(DISTINCT ({Outdated New Hires Range 4}, Number@row)

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

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

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

Christiana Gkini

Hi Andree,

Thank you for getting back to me. I did try that but it doesn't take me anywhere. Any other ideas?

Andrée Starå

@Christiana Gkini

Happy to help!

You're adding more characters to your formula.

See here (bolded)

= INDEX(DISTINCT ({{Outdated New Hires Range 4}}, Number@row)

Try and copy and paste mine as it is.

= INDEX(DISTINCT ({Outdated New Hires Range 4}, Number@row)

Did that work?

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

Paul Newcome

Yes. You have an extra set of {curly brackets} that should be removed.

Christiana Gkini

Hello,

Thank you for this.

I did update that but still doesn't work. Any ideas?

Christiana Gkini

Is there any chance I could have some assistance over a zoom call?

Thank you!

Genevieve P.

Hi @Christiana Gkini

Formulas can be tricky because their syntax requires exact precision or you'll receive an error. A comma out of place, or an extra bracket, or an extra space can cause an error.

In your image above I can see spaces between the functions. Can you try writing it all together?

For example, instead of

= INDEX (

you'll want to write:

=INDEX(

For a full formula structure like this:

=INDEX(DISTINCT({Range}, Number@row))

I also see that your "Number@row" isn't highlighting. Do you have a column called "Number"? You'll want to replace that portion of the formula with your Column Name to the left that houses the number.

Let us know if this works for you, now!

Cheers,

Genevieve