formula building up lead scorecards

Joerg Schmidt

Dear all,

I have a sheet "lead management" with columns like "number employees", "sector", "region" and many others - all of them as dropdown lists

Now, I want to create another sheet with the score criteria and score values f.e.

column "number employees" // column "region"

zero employees = score value "-10 // region north = value "10"

1-5 employees = score value "0" // region west = value "5"

6-10 employees = score value "+5" // region east = value "0"

11-50 employees = score value "+10" // region south = value "-5"

what should be a formula to catch the values and combine them with the defined scores?

should I build some helper columns such as "region helper" ?

Hope I did express my ideas comprehensively ?

kind regards Joerg

Andrée Starå

@Jörg Schmidt Novum Hospitality

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

It seems that the score is empty.

Andrée Starå

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

How would you want this to work?

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?

Joerg Schmidt

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" ?

Andrée Starå

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!

Joerg Schmidt

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 ?🤔

Andrée Starå

@Jörg Schmidt Novum Hospitality

Me again! Haha!

Happy to help!

Can you share a screenshot or the sheet?

Joerg Schmidt

Andrée Starå

@Jörg Schmidt Novum Hospitality

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

It seems that the score is empty.

Joerg Schmidt

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..😁🤩

Andrée Starå

@Jörg Schmidt Novum Hospitality

Haha!

You're more than welcome!

Joerg Schmidt

Hi Andree,

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

Joerg Schmidt

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

Paul Newcome

What exactly is it you are trying to have output?

Joerg Schmidt

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

Andrée Starå

@Jörg Schmidt Novum Hospitality

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

Andrée Starå

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

How would you want this to work?