How can I add data belongs to filtered columns in excel (using pivot table or filter function) and get update when change base row data for new month? Filtered data will refresh but data which I added beside filtered columns does not go down and remain at the first row. Moreover, I could not make a table from the filtered data and added data beside them or add to data model and make relationship.
More explanation of the problem:
There is no problem with my base data which are updated each month. But every month in base data, we will assign each rows to one person to analyze and I have used "Filter function" to show assigned data to each person in different sheet names. For analyzing, they need to add columns to filtered data, such as data validation filling, comment,... and I need to relate theses added columns to filtered array. So every month I will not have problem with updated data and I can upload the analyzed data to Power BI. Would you please help me to understand the right way of doing this.
Best Regards
Hi Reza,
I suggest creating a new file for each user, with data organized in defined tables, then aggregate user files into reports.
Typing data outside filtered area is not a great idea, should avoid it.
Thanks for your response Dear Mr. Bombea;
If I do your suggestion, What could I do for refreshing the updated main data after each month?
Best Regards
As mentioned, aggregate user files into reports, you no longer refresh the original data, just the data split into user files.
Not sure I understand what you mean, but you cannot put the changes back into the original data, there is no refresh for that. If this is what you want, you'll need vba code written for your unique scenario.
Thanks for your time and kind help.
