Fomula cross sheets | lead scoretable

Joerg Schmidt

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?

Julio S.

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

Julio S.

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

Joerg Schmidt

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 😁