Employee resume form is the most commonly used form in HR
The leader arranges a job that gives you a headache and converts a company-wide employee resume form into a standard Excel spreadsheet within an hour. ( For the convenience of demonstration, only 3 tables are set )
If there are hundreds of people in the company and manual copying, let alone an hour, you probably can't finish it in a day.
Don't worry, Lanse teaches you a quick conversion method, which can be done in 5 minutes!
Steps:
1. Extract all worksheet names
data - get data - from workbook - load into power query editor.
Delete other columns, hide the merged table, and then import back to column A of the Excel table
2. Set the extraction formula
Extract name formula (cell B2):
=INDIRECT($A2&"! b4 ")
Formula description:
indirect can convert literal expressions into references, and the name to be extracted is in cell B4 of the table, so $A2&"! b4 " can dynamically refer to cell B4 of the specified table ( the value of column A )
In the same way, set the extraction formula for other columns to complete
After finally copying the formula down, the information extraction of all tables is completed!
Lanse said : indirect is a very magical function, it can set a dynamic reference address, and then complete complex data extraction across tables and Excel files.
Press and hold the QR code picture below, click on " Identify the QR code in the picture ", and then click Follow, you can receive a newly written excel tutorial in blue every day.