Automatic consolidation of data from multiple sheets

Hello all,

I have data that varies in string length across sheets but I would like to consolidate it based on name. Ideally, a pivot table would make sense to me but I have never used one across sheets (if it's even possible). Anyways, if there's a clever way to do this, that would be immensely helpful. I've attached a sample sheet I'm working with. This is very simplified, but assume that the shaded, gray fields are locked. Essentially, this is simulating that is getting pulled from software.

Fruit.xlsx

What data are you attempting to consolidate and where you do you want the consolidated data to appear?