Index/Match always returns the same

Jose da Silva

Hi all,

Been searching on the community but could not find an answer to my issue. Sorry if i missed it.

So i have a Master sheet for the sales team, with all the customers to contact, with one rep allocated on each of those customers.

Then, the individual reps will have their own spreadsheets with only their allocated customers.

What i am trying to do now is, everytime that a new customer gets allocated to a rep on the Master sheet, i want it to be updated on their individual spreadsheets automatically.

I tried to do an INDEX/MATCH with the customer code, but the formula keeps repeating the same code, which means that it disregards if there are more results on the Main sheet. It finds one and stops. Here is the formula:

=INDEX({Leads Master Sheet Range 1}, MATCH("Emma Parnham", {Leads Master Sheet Range 3}))

...where:

Leads Master Sheet Range 1 - customer code column on the Master

Emma Parnham - rep name (will vary amongst a dozen)

Leads Master Sheet Range 3 - rep name column on the Master

 

Appreciated for the help!

Andrée Starå

Hi Jose,

Yes, the value has to be unique so you would need to make it unique with a combination of fields or row id if possible.

Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Jose da Silva

Hi Andree,

In the screenshot you can see the first half of the headers on the Main sheet (which are the ones that matter for this particular situation).

The customer code will always be unique (there is no particular coding based on the allocated sales person)

What i want to do is, using only the main spreadsheet, which contains a full list of all the customers to contact, give to the sales manager the power to add or remove customers from the individual sales reps, using only the Main sheet.

Everything else that i need is already automated. Just this small/big detail missing.

Paul Newcome

Have you tried looking into individual Reports instead of individual sheets?

Jose da Silva

That is not an option (unless someone comes up with an idea) because the individual spreadsheets are for the sales team to use individually. They are not just a "display"

All i want to do is, everytime the sales manager allocates a new customer on the mastersheet, that customer gets imported to their sheets

Paul Newcome

"They are not just a "display""

 

Reports can be edited. Granted the salesperson wouldn't be able to add new rows, but any rows that are populated on the report from the master sheet can be edited as needed.

.

Otherwise you will need to use multiple cross sheet references with INDEX/MATCH formulas or a 3rd party tool such as Zapier.

Andrée Starå

Paul is correct!

Cross-sheet formulas or Zapier is the way to go. We could set Zapier up to copy the row to different sheets depending on the responsible sales rep.

Would that work?