formula building up lead scorecards

Have you added the Score table as in my screenshot or similar?

It seems that the score is empty.

https://community.smartsheet.com/discussion/comment/338269#Comment_338269

How would you want this to work?

All comments

Paul Newcome

What are the actual dropdown options? Are you able to provide a screenshot of some sample data for reference? Are you wanting to add or multiply?

Hi Paul,

thanks for taking this challenge :-)

I put two examples "number of employees" and "customer type" (instead of region)

You may assign the values to the examples as you want.

It is only the formula I am struggling with - as always :-)

Now, I am curious to see what it will be...

Mitarbeiteranzahl.jpg

Wouldn´t it be better to "catch" the results of the formula either inside the same or a new sheet - lets say "lead scorecard" ?

Hi @Jörg Schmidt Novum Hospitality

I hope you're well and safe!

Here's one way to solve it in the same sheet. (I'd recommend keeping it in the same sheet if it's only this sheet that would use the Score Table)

Try something like this.

Create the Score Table.

Here's the formula.

=
INDEX([Number of Employees (Score)]:[Number of Employees (Score)], 
MATCH([Number of Employees]@row, [Number of Employees (Table)]:[Number of Employees (Table)], 0))
 + INDEX([Customer Type (Score)]:[Customer Type (Score)], 
MATCH([Customer Type]@row, [Customer Type (Table)]:[Customer Type (Table)], 0))

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Hi Andree,

you again :-) Thanks for helping

I tried this in first step and at least there is no default "unparseable" - cell is simply blank without any result.

=INDEX([Kundenart (Score)]:[Kundenart (Score)]; MATCH(Kundenart@row; Kundenart:Kundenart; 0))

Both columns "Kundenart" and "Kundenart (Score) are dropdown list columns.

In the result column "score value" I have "text/number as column type.

The three columns are not next to each other but in the same sheet - no problem, I guess

Any idea ?🤔

Me again! Haha!

Happy to help!

Can you share a screenshot or the sheet?

Have you added the Score table as in my screenshot or similar?

It seems that the score is empty.

Why is almost everything so easy with Smartsheet ?? :-)

I did not understand on first sight that I simply can put a table inside my sheet.

Thank you so much for helping again.

You made my day again..😁🤩

Haha!

You're more than welcome!

Hi Andree,

one more question concerning this issue. Is it also possible to collect values from a multi dropdown as following

@Andrée Starå could you please help again here?

Paul Newcome

What exactly is it you are trying to have output?

Hi Paul,

I decided to put the lead criteria in another new sheet called "Leadkriterien"

In the original sheet "lead management" I make the entries for every lead or customer such as "customer type", "marketing actions", "customer relevance" and so on. I add a value to every entry. Now, I try to have the formula over these two sheets in order to have a score value

Two questions are left:

1) how to change the formular to get a correct value

2) is it possible to take values from a multi dropdown list?

=INDEX({Leadkriterien Bereich 1}[Kundenart (Score)]:[Kundenart (Score)]; MATCH(Kundenart@row; {Leadkriterien Bereich 1}[Kundenart (Übersicht)]:[Kundenart (Übersicht)]; 0)) + INDEX({Leadkriterien Bereich 2}[Marketingmaßnahme (Scorewert)]:[Marketingmaßnahme (Scorewert)]; MATCH(Marketingmaßnahme@row; {Leadkriterien Bereich 2}[Marketingmaßnahme (Übersicht)]:[Marketingmaßnahme (Übersicht)]; 0))

Meetings, but I'd be happy to take a look now.