Excel – How to automatically update values referenced from one sheet to another in excel

Option 1: Skip the requirement that column G must be alphabetized. Delete rows by marking them as deleted and always insert rows at the end of the sheet. This of course to keep the external references stable.

Option 2: Change the external references to a key value (unique identifier for a specific row) instead of a specific cell and use VLOOKUP or similar to retreive values from column G.

=VLOOKUP("uniqueId";[WorkbookA]Sheet1!A1:G1000;7;FALSE)

Your question is very vague, but I think I understand it.

Basically you have a spreadsheet which gets information from a spreedsheet in another document and then does something with it. What you want to do is do the exact same thing for information taken from 100 different documents.

To get information from a spreedsheet in another document you need to use the following syntex in your formula...

='[DOCUMENT-NAME]SPREEDSHEET-NAME'!CELL

For example, to get the information in cell A1 in spreedsheet Sheet 1 from document one you need to write:

='[one]Sheet 1'!A1

Now you "just" need to change the information in each formula as needed, how you do this all depends on your naming system. Without more information about the names of each documents and the spreedsheets used it is impossible to give you more help.