Excel IF/FILTER formula

Question

Hi there,

I have 2 sheets in my Excel file. I use the "Filter" function in the Sales Sheet to filter clients/products marked as 'Potential Sales-Yes'. However, I also want to add notes and follow-up information in the Sales Sheet. The issue arises when I add comments in columns O-R of the Sales Sheet, and update the 'Yes' (e.g., by adding Master Sheet cell N5) after those comments, the Sales Sheet Column O-R becomes fixed, and the data is no longer correct. I've tried using IF functions, but it still doesn't work. Could you advise if there is another function I can use to resolve this?

Thank and regards, YY

Answer 1

User power query to get data from master sheet.

Data>Get data from range>Transfrom Data>filter "Yes">Delete unnessary columns> Load table.

Then you can use selft referencing to add notes based on power query result.

Self Referencing Tables in Power Query 🔗- Excelerator BI

Answer 2

Dear Snow,

Please correct me if I’m misunderstanding your suggestion.

Are you saying I should manually enter all the data in a separate sheet?

If so, how can I link this Sheet 3 with the Master Sheet "Yes" into the Sales Sheet?

Answer 3

Add another sheet to put comments only and use formula to get comments from third sheet to sales sheet.

3rd sheet, Column A and Column B should be static value.(Unique ID)

Company	Name	Notes
A1	B1

Answer 4

Dear Snow,

Are there any other options or methods I can use instead of adding comments to the Master sheet?
My Master Sheet will be too much column and become messy

Thanks, YY

Answer 5

Filter formula is dynamic and your notes is static. Formula cannot match them.

You will need to add comments in master sheet instead of sales sheet. Then you can get the data you want use filter formula.