getting data from other excel file with same sheet name

Question

i have two excel files , where i enter sales figures , and make sheets date wise . . . is there any way , that once i enter sales figure in sales file , in a new sheet every day with date as sheet name , that sales figures gets automatically updated in my stock excel workbook , with same date ??

Answer 1

Hey there, my name is Ajibola and I'm excited to help you out today!

Yes, you can automatically update sales figures from one Excel workbook to another based on the date as the sheet name. To do this, you can use Excel's linking or referencing features to establish a connection between the two workbooks. Here's a step-by-step guide:

Assuming you have two Excel workbooks:

1. Sales Workbook (Where you enter sales figures):
   • In this workbook, create a new sheet for each day and name each sheet with the date (e.g., "2023-09-08" for September 8, 2023).
   • Enter the sales figures for each day on the respective sheets.
2. Stock Workbook (Where you want the sales figures to be updated):
   • Create a new sheet where you want the sales figures to appear.
   • In this sheet, enter the dates in column A (e.g., starting from A2 down) that correspond to the dates in your Sales Workbook sheets.
   • In the adjacent column (e.g., column B), use a formula to reference the corresponding sales figures from the Sales Workbook. You can use a formula like this:

   ='[Sales Workbook.xlsx]2023-09-08'! B2

• Replace "Sales Workbook.xlsx" with the actual name of your Sales Workbook and "2023-09-08" with the date of the sheet in the Sales Workbook you want to reference.

3. Automatic Updates:
   • Now, when you open the Stock Workbook, the sales figures from the Sales Workbook for the corresponding date will automatically appear in the Stock Workbook.
   • Whenever you update the Sales Workbook for a specific date, the changes will automatically reflect in the Stock Workbook as long as both workbooks are open.

Remember to save both workbooks and keep them in the same location to maintain the links between them. Also, ensure that the sheet names in the Sales Workbook match the dates in the Stock Workbook.

If you have any further questions or need additional assistance, please feel free to ask. I'm here to help!

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Kind regards