Deleting/Adding rows from source data outputs "#REF" error in the consolidated table

[Dadashvespek]

I have a sheet of recipes that contain ingredients and ingredients repeat for each recipe. After consolidating the data into another sheet, it combines duplicates and updates if I update numbers in source which is good. But if I want to add/remove a recipe which occurs very frequently, I would need to re-consolidate the whole table again or else I'll get a #Ref error in the ingredient I deleted. And an added ingredient doesn't add to the consolidated list. So I think the solution would be to create a vba that automatically consolidates everything into the summary sheet. I have searched multiple forums and many tutorial videos as my knowledge is very limited in vba. And the only success I've had is a code that consolidated the data but did not combine duplicates. I have attached the file I used but for simplicity I cut about 90% of the actual list. Any help regarding this is appreciated, maybe there is an alternative solution that doesnt require vba, I read something about the use of aggregate instead of sum, but couldnt figure that out.

cross-posted to vbaexpress but cant add link since new profile

[teylyn]

How did you arrive at the data in Sheet3? Was that manually typed in, linked to the Source sheet and then grouped? Instead of doing that, you can create a Pivot Table from the Source data.

When data is added or removed in the Source sheet, you only need to refresh the pivot table.

[Dadashvespek]

I used "consolidate data"

[teylyn]

Create an Excel Table from your data with Insert > Table. Then create a pivot table with Insert > Pivot table. That will make your life a lot easier.

[Dadashvespek]

I tried that, but the table doesnt update as I change the ingredients

[teylyn]

- did you refresh the pivot table? Data will not refresh until you hit that button
- does the pivot table source data range contain all the data? Turn your source data into an Excel table to ensure that. If you don't use an Excel table, the process of automatically including new data is a lot more complex.

[Dadashvespek]

Yeah, that works for me now! Thank you teylyn!