No. It can’t be possible.
So! What’s the solution?
Well, it lies in the automatic update. Won’t get the idea? Ok, let me explain. How is it if one file is updated and the attached file would register the changes automatically? Isn’t it a great idea!
Excel allows you to do so. An update in one spreadsheet will reflect in the attached spreadsheet. You need not browse the files separately to add a new data entry. Now, you can easily anticipate how most of the data outsourcing companies capitalize on this excel auto update from another worksheet in the data entry projects.
Want to know how you can do so? Here are the steps that can do the trick for you to transfer the data from one excel worksheet to another automatically:
1. Get ready with two separate worksheets. You can open the two in the same excel file or separately.
2. Copy the captions or the headers of each cell. Keep the sequence similar so that the entries would be identically added.
3. Now, you need to craft a button on the worksheet that can fetch the updated data entry from the source worksheet. This button will set up an excel link to another file or sheet for transferring the data automatically. To create it:
- Go to the file menu.
- Select a shape, like a square or rectangle to embed the link.
- Drag the mouse where you want to let the button sit in the excel sheet.
- Add the name, like Transfer Data, Update Data or any other desirable name.
- Align the name in the middle.
- If you want to change its formatting, you can right click the shape and select the ‘format shape’ option. This is how the button to insert the new & a seamless data entry would be ready.
- Click the ‘Developer’ menu.
- Select the ‘Record Macro’ option from the top left of this menu.
- A dialog box will appear. Change the default name desirably. It should be the same that you have input over the created shape. I named the shape as ‘Update’ earlier. So, I’ll assign the similar name under macro name field.
- If you want to define the shortcut key, you can add any with the Ctrl + ……
- Click ‘Ok’. The macro will initiate recording. The ‘record macro’ option will change into ‘stop recording’ in the developer menu bar.
- Further, select the cells in the worksheet that you want to use as the source data. Those cells can be blank. Copy them. Later, these cells would be the source of the data entry to the linked excel sheet.
- Switch to the target sheet where you want to transfer the data automatically.
- Go to the cells under the headers where you want to reflect the changes. Select.
- Right click and choose the ‘Insert Copied Cells’ option from the dropdown menu.
- Then, stop the recording of the macro. For it, switch back to the source worksheet & click the macro to stop recording.
- Stay on the same sheet. Right click the ‘Update’ tab (that I created).
- Select ‘Assign Macro’ option from the dropdown menu. Its dialog box will open.
- Select the name of the macro (Like I captioned mine as Update).