Excel Forum

Add data belongs to filtered columns

Reza Amini

Active Member

Members

Forum Posts: 4

Member Since:
January 3, 2024

Offline

January 3, 2024 - 7:46 pm

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

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

January 5, 2024 - 3:52 am

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.

Reza Amini

Active Member

Members

Forum Posts: 4

Member Since:
January 3, 2024

Offline

January 5, 2024 - 3:27 pm

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

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

January 6, 2024 - 4:00 pm

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.