Aggregate Multiple Dataheets into One Datasheet that Will Update Automatically

kldinnm

I have multiple datasheets with multiple records in each datasheet. Each record has a unique ID. Currently when a new record is added to a datasheet, it is automatically copied into the aggregated datasheet. I want specific fields for each record copied into the aggregated datasheet to automatically update when updates are made in the source datasheets. Is this possible and can you suggest how to do it? I thought of copying the row automatically and then using an index match function, but because multiple sheets are being aggregated the formula for the index match will have to be manually changed. Any ideas how to get around this

SoS | Dan Palenchar

Hello @kldinnm

A simpler solution may be to create a report that pulls together all of your datasheets into one view with only the column you want to see.

If you need a separate destination Sheet and want some of the column to update based on the original source data, your options are:

• Formula such as INDEX(MATCH())
• DataShuttle
• Control Center (via Blueprint Summary)

The best solution depends on your specific system architecture and needs.

kldinnm

Hi Dan! Thanks for the response. The users of the aggregated sheet need to see all the columns from the source sheets. I will try the index/match for the columns that will be updated. I'm just confused about how automate the index/match as it will be pulling from different source sheets. Because of this I can't make it a column formula.

kldinnm

Hi! I decided to aggregate the datasheets in a report so that the users of the report will get updates. Therefore, this question has been answered.

Andrée Starå

@kldinnm

Glad you found a solution!

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!