Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Allowing users to add data to a workbook that uses Filter Function, which will re-sort with source

Copper Contributor

Hi all,

 

Really hoping someone can help - I haven't been able to find an answer online. 

 

I have a "master" workbook, which for example, contains information for all ASM's customers. I then have copies for each ASM, which uses the "Filter" formula to return only the customer information for their area based on that criteria. 

 

This all works fine, but the issue I'm having is:

In the ASM's filtered copies, I also want to provide columns for specific feedback/information to be provided by the ASM themselves. I would then have my Masterfile vlookup into their filtered copy to collect this information.

 

However, if the data in my Masterfile changes, i.e., a row is removed (which will happen intentionally for many reasons), of course this means the data in the ASM's copy will re-sort if any of their customers were removed from the source data. Therefore, any information they added into the additional column I provided, will now just assign to the next customer inline.

 

Example - so if below was the Master Copy/Source Data:

Customer Code

Customer Name

ASM/Region Name

FAR02

Fairy Tale Construction

Joe Bloggs

MAL01

Made Up Lands

Joe Bloggs

TTH12

Example Made Up

Joe Bloggs

JUL08

July Bathrooms

Margery Simps

TEG56

Trip Up

Margery Simps

And below was the ASM's copy, using "FILTER" function:

Customer Code

Customer Name

ASM/Region Name

ASM Commentary

FAR02

Fairy Tale Construction

Joe Bloggs

Will be having a sale next month

MAL01

Made Up Lands

Joe Bloggs

May close

WTTH12

Example Made Up

Joe Bloggs

Would like follow up on XYZ

 

I would want to collect the information from the 4th column in the ASM copy. However, if we remove the 2nd customer, MAL01, from the source data, the ASM's filtered copy will remove this customer, but the comment will remain - and it will instead assign to the next customer, and the comment that was for the following customer will move down, and so on. 

 

Is there any way that I can ensure this doesn't happen? Can I set it that for example, cell D2 will follow with "Fairy Tale Construction" wherever it goes? 

 

1 Reply