How do I get data from Sheet2 to Sheet1 in Excel?

I've searched everywhere for a clear answer to this but I can't find any and it seems like such a simple problem:

Let's say I have 6 sheets. The first is a summary page, the other 5 are named "Monday", "Tuesday" etc thru "Friday". I've set up these 5 day-of-the-week sheets with the same exact tables, only with different values in the cells from day to day.

In A2 on the summary page I want to write a long, complicated formula where Monday occurs several times in calculating Monday's results. (i.e. =((Monday!A1*2)+(Monday!A2*3)/LN(2))-(Monday!A4/Monday!A7) etc etc...)

To see Tuesday's results, I want Excel to change the data in that summary sheet's formula automatically so I don't have to delete Monday and type Tuesday 10 times. I'd rather type the word Tuesday into A1 and have the complicated formula reference A1 and know to change the text of Monday into Tuesday in all the instances in the formula. Does that make sense?

It seems excel should have this function since it would save a lot of typing, especially if I have 100 different sheets..

Thanks!
Charles

Dirk says:
January 17, 2022 at 7:02 pm

Hi,

I'm using the latest Excel version via Office365, so I can use data ranges converted to tables.
My first workbook contains several sheets, each with a specific table. Data is referenced via the x.lookup function to table fields instead of row/col ranges. Works great !
This workbook is used as a data container and is read-only (can only be edited by myself).

A second workbook is pulling data from the first one , also by using the x.lookup function and extracting data from the different sheets based on specific parameters.

Both workbooks are stored on OneDrive in the same folder.

It all works fine as long as the first workbook (the data container) is opened on my PC. When I close this file, The second workbook gives error messages as result for the x.lookup referring to the tables. It seems that the references in the x.lookup function are not recognized any more.

Is this normal behaviour ? Can I extract data from (dynamic) tables in other workbooks when these files are closed ? I hope this can be done without the use of PowerQuery (which is also a great tool).

Looking forward to your reply.

JJ Lee says:
February 25, 2021 at 5:47 pm

Thanks for the reply Alexander,
however this doesn't look like what i'm trying to do. Youre example & articles indicate me doing a Hyperlink to the other file on the internet.

I'm trying to only pull the value of a cell on another workbook on the internet. Not a hyperlink to the other file.
For example)
1. Workbook A - I have a main dashboard tracking multiple tasks/projects
2. Workbook B,C,D,E,F - Each project has its own separate file on the internet (in box.com)
3. Anyone can update Workbook B,C,D,E,F as they work on stuff and the % of completion will show up on the top in sheet1 cell A1
4. For Workbook A - I want to be able to pull the value in each of the other workbooks Sheet1 CellA1 value, so it gives me a dashboard % of all other workbooks at once instead of me having to go open all the other files one at a time.

Hopefully this example makes sense.

Any suggestions will be greatly appreciated!

Nav says:
September 3, 2020 at 4:33 pm

We have an Excel Workbook with 10 to 15 worksheets and all these worksheets interacts with one another for calculations and for data. Some of these worksheets gets Data from other excel workbooks by a cell reference formula. The main workbook depends on worksheets and other excel files/workbooks to get data and do calculations this work is done once a year. So many references, formulas and calculation are involved. Each year we copy all the last year workbooks and update the source files to do the calculations for the present year. The master excel workbook mostly does the calculation by itself when the source data feeds into the workbook and there are some excel cells in the master workbook that needs data from user but this is rare.

Is there a way to track all the changes happened in the source files that got feed into master workbook, like old values in source values that got updated to new values and also capture old and new value in master book for comparison. Tracking should capture values not formulas.

Nav says:
September 9, 2020 at 2:41 pm

Hello, thank you for quick response. I tried track changes within excel but this doesn't work in my situation. If i have an empty cell in master excel and if i have a formula that gets value from other excel file track change shows old value as 0 and new value as formula.But if i update source file with different value track change wont capture this because formula will be same. We have some where around 12 source excel files that we update value and this data will feed into master excel file

I am new to last changed cell concept but i want to track each and every cell that got updated in source file with old value and new value. This data will feed into master workbooks and again i want to capture old and new value so we can just manually check the two log sheets to make sure all the data is correct and feed properly into master excel file. I am hoping there will be way and thanks for future response.

Alexander Trifuntov (Ablebits Team) says:
June 25, 2020 at 9:35 am

Hello Heather!
If Excel uses an external reference to another file, it tries to recalculate it to get accurate final data. If Excel can’t do this for some reason, it doesn’t consider the file version as final and doesn’t get anything from it.

Thus, if any additional actions are required from a user to recalculate the data in the source file, you won’t get the data from this file until the user opens it and allows to update the data or run macros. Most likely, the file from which you pull the data contains some external references as well. Unless the file is open, the references can’t be updated. Therefore, it is not possible to automatically recalculate the data. Besides, there may be macros in the source file that also require an additional permit to be run. While Excel is waiting for this action to take place, it doesn’t transfer the data from this file to other files.

How do I get data from sheet 2 to sheet 1 in Excel?

How do you use Excel to pull data from another sheet?

How do I pull data from another sheet in sheets?

How do I link Sheet 1 to Sheet 2?