Google Sheets Automation for Supply Chain

Hey there! We need guidance for organized and automated google sheets for our Supply Chain Department where employees enter the data in five different google sheets. It should highlight errors when data entered is abnormal. A single sheet is required to extract data from the above five sheets for data analysis. New Stores and Store Ids are added to the sheets daily. Also, an excel macro is needed to automate few Excel sheets.