If + Index:Match for integrating various sheets

Jörg Schmidt1

Dear Community,

currently I use one sheet "lead management ("Kontaktübersicht") to collect all data from a form

In this form the interested party has to answer about 10 questions in order to get a price for product 1 (Angebot Premiumkurs")

At the moment, this means I have 10 columns in my sheet ("Kontaktübersicht") which makes it rather confusing - although we use report for our daily work

For another 2 products I probably would need at least another 10 additional columns and so on.

Maybe, we have 10 products in future and we would like to have price configurators for all of them.

Our idea is to have the prices calculated in separate sheets (f.e. configurator product 1). But in this case I would have outsourced data in slimmer sheets but I cannot copy data from configurator sheet into general overview with the personal data of the interested party.

preferably, I would collect data in one form, distribute them in the single configurator sheets and catch results again in the total overview.

Do you have an idea how to solve this without letting the formula IF (Index:Match) explode ? 🤣

Right now, everything works smooth with the above formula but if I would add another product everything is going to explode - right ?! 😎😏

Kelly Moore

Hello @Jörg Schmidt1

Your formula wouldn't necessarily have to explode - depending on how you have your sheets set up.

In the sheet above you hard-coded the name of your product into the formula. If the product name was in a cell (probably a dropdown column) then you could use [Name of your product column]@row in place of your "Angebot Premiumkurs" term in your formula. (No quotes around the [Name of your product column]@row reference).

You could create a reference sheet to use as a lookup table for your Index/Match (or maybe Index/Collect) using the product name as the identifier (a product code might be better if available). Depending on what is being used in your calculations this lookup table could house the calculation factors specific to the product. You would bring these factors back to your Sheet 1 (or the Calc sheet if you're using that)

To answer your question about calculations on yet a different sheet- you probably could do that (is it necessary?) using the copy row automation. This is one of the easiest ways to keep sheets in sync with one another but often creates excess columns on the destination sheet as all columns from the 1st sheet will be moved - I don't think this is a problem for you as you can hide what you don't need (if any) and you're not near any sheet limits on columns or cells).

If it were me, (#1 make a copy of your existing working sheet and use it as you play with formulas) I'd see if I could just use the approach of a Product Reference Lookup and the existing sheet to do all future calculations. Remember your users may not need to see all the columns in your existing sheet - or change the order so that final columns they might be interested in are close to the left side. If only using a Product Reference & Existing sheet seems too complicated after trying then I would add the calculation sheet. I would try Copy Row automation as my first attempt. You can add additional columns - that is the calculation sheet can additional columns more than the Copy Row is bringing with it.

Others in the community may have additional approaches. Let me know if any of the above sound like something you wish to try and you need some guidance setting it up.

Ich wünsche ihnen einen wunderbaren Tag (did I get this right?)

Kelly

Kelly Moore

Hello @Jörg Schmidt1

Your formula wouldn't necessarily have to explode - depending on how you have your sheets set up.

In the sheet above you hard-coded the name of your product into the formula. If the product name was in a cell (probably a dropdown column) then you could use [Name of your product column]@row in place of your "Angebot Premiumkurs" term in your formula. (No quotes around the [Name of your product column]@row reference).

You could create a reference sheet to use as a lookup table for your Index/Match (or maybe Index/Collect) using the product name as the identifier (a product code might be better if available). Depending on what is being used in your calculations this lookup table could house the calculation factors specific to the product. You would bring these factors back to your Sheet 1 (or the Calc sheet if you're using that)

To answer your question about calculations on yet a different sheet- you probably could do that (is it necessary?) using the copy row automation. This is one of the easiest ways to keep sheets in sync with one another but often creates excess columns on the destination sheet as all columns from the 1st sheet will be moved - I don't think this is a problem for you as you can hide what you don't need (if any) and you're not near any sheet limits on columns or cells).

If it were me, (#1 make a copy of your existing working sheet and use it as you play with formulas) I'd see if I could just use the approach of a Product Reference Lookup and the existing sheet to do all future calculations. Remember your users may not need to see all the columns in your existing sheet - or change the order so that final columns they might be interested in are close to the left side. If only using a Product Reference & Existing sheet seems too complicated after trying then I would add the calculation sheet. I would try Copy Row automation as my first attempt. You can add additional columns - that is the calculation sheet can additional columns more than the Copy Row is bringing with it.

Others in the community may have additional approaches. Let me know if any of the above sound like something you wish to try and you need some guidance setting it up.

Ich wünsche ihnen einen wunderbaren Tag (did I get this right?)

Kelly

Jörg Schmidt1

Hi Kelly,

your final greeting in German was excellent :-) same to you.

I leave this still a bit open, just in case I have another question concerning your helpful and most valuable hints.

Now, I try to find an approach to this issue.