Batch Rename Files based on cell

Batch naming of files based on cell value in Excel

While performing compilation or consolidation of data across different regions, there is a need to standardize the data input from all the respective parties. In a spreadsheet itself, this data integrity control could be enforced through validation drop-down boxes >> Users are only allowed to enter from a list of "validated & authorized" entries

However, unlike the cells within a spreadsheet where one can control through a standard template, the control over the naming conventions of the final completed files submitted by others is not enforceable. 

In most cases, the files submitted will end up in a folder where the person doing the consolidation will perform a completeness check on whether all entities have submitted their reports.

The naming convention of files received are not in a consistent manner rendering it troublesome to track the current status of report submission by the different parties. (Requires opening up every file to verify before renaming them one by one.)

The reports itself is a standardised fixed structured template 

For such issues, the below BatchReNamingFiles spreadsheet could be helpful to facilitate such batch renaming of files

PS: The tool can capitalize on the fact that every file  is a standard formatted template. (E.g Cell C2 of the first sheet of every file contains the entity code. Will tag this unique entity code to the name of the file)

Using the BatchReNamingFiles tool

Click on "Select Folder Location" to select the folder housing all the files.

Upon selection, the details of all files residing in the selected folder will be imported into the spreadsheet as per below.

PS: The tool will return a list of all excel files in the selected folder. If sheet specified (Sheet1 as per below example) does not exist in any of these spreadsheets, Field X will simply return #REF! in the respective rows.

Download File