Fomula cross sheets  lead scoretable
For any reason this formula does not yet work properly. Has anybody an idea?
Furthermore, do you have proposals for more KPI´s or sheets you can share?
Best Answer

Hi @Joerg Schmidt,
It seems like you'd like your formula to return a sum of scores for each category (Kundenart and Marketingmassnahme) using INDEX(MATCH()) formulas.
The first thing to note is that the formula syntax doesn't look correct, as each argument isn't correctly delimited. E.g the first argument in the INDEX function starts with a cross sheet reference continued by a Column range without any separator. Note, however, that even if a ";" would separate these arguments, the formula would still be incorrect as the second argument in the INDEX Function is a "row_index" which can't be a column range.
As an example of how to achieve similar results as the ones that you seem to intend, I used =INDEX({Kundenart Score}; MATCH(Kundenart@row, {Kundenart}; 0)) + INDEX({MarketingNahme Score}; MATCH(Marketingmassnahme@row; {MarketingNahme}; 0)) in the "Scorewert" Column below to return the sum of each "scorewert" value associated to each category:
The result of this would be the sum of each "Marketingmassnahme" and "Kundenart" category value. E.g for row 1; 90 (10+80), for row 2; 110 (20+90) and so on. If you'd like to expand on the formula to take on further KPIs you can continue adding INDEX(MATCH()) arguments following the structure above.
In addition to the above, I'd suggest reviewing the MATCH Function for further indications about the correct syntax of this function.
If this can't help achieving what you intend or I haven't understood what you would like to accomplish, please add a brief explanation of what would be the intended result you'd like to see in the "Scorewert" and an example of the current result you are obtaining after updating these accordingly as well as further screenshots of each cross sheet reference (values coming between {}).
I hope this can be of help.
Cheers!
Julio
Answers

Hi @Joerg Schmidt,
It seems like you'd like your formula to return a sum of scores for each category (Kundenart and Marketingmassnahme) using INDEX(MATCH()) formulas.
The first thing to note is that the formula syntax doesn't look correct, as each argument isn't correctly delimited. E.g the first argument in the INDEX function starts with a cross sheet reference continued by a Column range without any separator. Note, however, that even if a ";" would separate these arguments, the formula would still be incorrect as the second argument in the INDEX Function is a "row_index" which can't be a column range.
As an example of how to achieve similar results as the ones that you seem to intend, I used =INDEX({Kundenart Score}; MATCH(Kundenart@row, {Kundenart}; 0)) + INDEX({MarketingNahme Score}; MATCH(Marketingmassnahme@row; {MarketingNahme}; 0)) in the "Scorewert" Column below to return the sum of each "scorewert" value associated to each category:
The result of this would be the sum of each "Marketingmassnahme" and "Kundenart" category value. E.g for row 1; 90 (10+80), for row 2; 110 (20+90) and so on. If you'd like to expand on the formula to take on further KPIs you can continue adding INDEX(MATCH()) arguments following the structure above.
In addition to the above, I'd suggest reviewing the MATCH Function for further indications about the correct syntax of this function.
If this can't help achieving what you intend or I haven't understood what you would like to accomplish, please add a brief explanation of what would be the intended result you'd like to see in the "Scorewert" and an example of the current result you are obtaining after updating these accordingly as well as further screenshots of each cross sheet reference (values coming between {}).
I hope this can be of help.
Cheers!
Julio

Dear Julio,
thank you so much. You crashed  hm.. I meant you saved my weekend 🤣 because now I can proceed
It works and I am happy. Isn´t it fun ?
Have a good time and please stay happy and healthy 😁
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!