How to Put Formula in Excel One Sheet to Another

227 comments to "How to create external reference in Excel to refer to another sheet or workbook"

Dax says:

Hi,
I have a sheet with Construction project details. On the other summary sheet, I need to select the project and few relevant date of selected project should appear. How can i do that? can you please help.
Thanks
Sergey says:

Hello Svetlana!

Suggestion -
In the section "External reference to an open workbook", add a troubleshooting note, that if selecting a range in the referenced file is not automatically generating the reference in the formula, then perhaps the excel files are open in different instances of excel. To confirm, open Task Manager, and see if all open Excel files are nested under a single instance of Excel. If not, close one file and open it again from the other file.

I just ran into this issue today. Found the solution here: https://answers.microsoft.com/en-us/msoffice/forum/all/cant-reference-a-cell-in-another-file/ce05b277-5b67-4fdf-ab8b-e63a1e7a610d

Thank you for your thorough educational articles. They have been an oasis for me over the years.

Regards,
- Sergey
- Hi Sergey,
  
  Thank you so much for this useful information and for your kind words! The tip about different instances is added. Thank you :)
Piebe Krol says:

Hello,

I have tried the reference to a range of cells but that didn't work:
=sheet1!a1:b8 on sheet2 in cell a1 results in only A1 from Sheet1, not the range.
I would like to have all referenced data A1:B8 from sheet1.

What's going wrong, i am using excel 2010.

A1:B8 is a table, is it posible to reference to a whole table?
- Piebe says:
  
  Solved.
  Doesn't work in excel 2010.
  Tried 365 '21 there it works: awesome!
Susanto says:

Hello!

Thank you for sharing, but I'm curious, because I work with a lot data, how to take address from other cell to be a task , for example if I want to take data from worksheet name A in cell B5, then I can type = A!B5, but I will do this in many times, so I think I can create colom wich fill with A (in cell A5) A A A A then I make another collom with B5 (in cell B27) B6 B7, then I don't want to type it one by one but want to drag it so it will be automatically take data, do u know how to do that, so I just type =A5!B27 , then the excel with take data from sheet A cell B5, but I can't just type that, would you know the formula? Thank you
- - Susanto says:
    
    hello, sorry my question is not clear, so here it is, I want to retrieve data from another worksheet, if typed manually it will take a long time because the data is a lot, so I want to ask if the =worksheetname!cellname command can be written by clicking on a cell another, so there is already another cell containing the worksheet and cellname in a different cell, so I made 2 columns, the first column contains the worksheet name, the second column contains the cellname name, if I can fill in the command to retrieve data by clicking on the cell in the worksheet name column and the cell in the cell name column, I don't need to write one bye one "address of the data to be retrieved but only need to drag it down. . is this possible? so the =worksheetname!cellname command is filled from 2 other cells.
    - Sergey says:
      
      Hello Susanto,
      You may have better luck using a lookup (my favorite is the INDEX-MATCH method).
      But if you really need it as you described - one possible solution for you may be the INDIRECT() function. So if your addresses are listed in column A, you can write the following formula in B1, and drag (or copy) it down: =INDIRECT(A1)
      
      Regards,
      Sergey
Mayvyn says:

I have a several excel files in a folder. Each file has the same 1st sheet named movement. I want to list each filename in a column and next to it the value from cell B79 in the movement sheet from each corresponding file. How can i do this without opening each file.
Matt says:

I have several sheets within a file. Each sheet has a formula to get data from the 'INFO' sheet. =INFO!I1
When the file gets sent to others and then the sheets are edited by different people and then all the sheets are copied back into the master file, this formula is messed up as it is trying to reference a sheet from a different file. How can I make this formula always pick the data from the 'INFO' sheet within this same file rather than going looking for an external file?
='[Sub Inspection Forms_R12_20210816.xlsx]INFO'!I1
C says:

Hi,

I have a workbook but some of the data is sensitive, whereas a lot of it is very useful for another department in my company. Is there a way to reference the relevant data in workbook 1 to a (supposed) workbook 2 and to have it update whenever we change data in workbook 1 without allowing file access to workbook 1?

Workbook 1 is saved in a SharePoint Library that only my department has access to for confidentiality reasons, and we cannot allow access to this file to anyone outside my department but we really don't want to duplicate work by copying and pasting data that already exists for our other department.

I hope this makes sense!

Thanks in advance.
Jennifer says:

I have a master workbook saved on my desktop that pulls totals from several workbooks that are on my companies sharedrive. Several people work within the workbooks being referenced and they often need to add rows within the worksheets. This unfortunately is messing up the reference every time. I have removed the "$" so they should be relative refences. It does not appear to be happening when I have the master workbook opened at the time of the row addition. I am not sure what I am missing. Below is an example of what the reference looks like. F48 is where the total sits when I create the sheet. When a row is added it still pulls from F48 although the total is now in F49.

='\B\2020\Timesheets\project\[07-2020.xlsx]NAME'!F48

Do you need more information?

Thanks!
- Hello!
  Links are automatically changed only within the current workbook. Links to external files are not automatically changed.
CS Davies says:

IF functions between workbooks

I wish to use the IF function by referencing cells in an external workbook. I have one cell in an external workbook that is to be used as the true or false test, and if true, I want to pull the contents of another cell in the same external workbook through to my current workbook.

I have used the link to the cell in the external workbook with the formula in the current workbook and it is not returning the result I want. The formula is

=IF('[Crabbet Park House_Project WorkBook_v1.0.xlsx]Actions'!$K$9="Open",'[Crabbet Park House_Project WorkBook_v1.0.xlsx]Actions'!$C$9,"")

Can anyone identify what is wrong?
Thanks
Chris
- Hello!
  I don't have your files. Therefore, I cannot check the work of the formula. You have not written what exactly does not work.
  For a formula with external references to work, the external workbook must be open.
Sandro Feliciano says:

some diferente problem.
Have about 10 workbooks that i need to refer, Each workbook has the same range like .=

=MÉDIA('D:\Users\local\[name1.xlsx]name1'!$GP$4:$XFD$4)
=MÉDIA('D:\Users\local\[name2.xlsx]name2'!$GP$4:$XFD$4)

The range change every day (so tomorrow my range will be GQ$4:$XFD$4) for each workbookl

How can I change the range GP$4:$XFD$4 to GQ$4:$XFD$4 for the multiples cells. I mean, how can I change only one istead 10?
- Hi!
  Try using the standard Excel "Find and Replace" tool, look in formulas.
  - Sandro Feliciano says:
    
    And thats something I never tried. And Works fine
    
    Thanks
Dean Cardno says:

Hi - I have a colleague who is trying to extract data from one workbook for use in another. Some functions work when the source workbook is closed (direct references, look-ups, transpose), while an indirect function does not. Is there a guide or list that indicates which functions will update correctly from a closed workbook, and which ones will not?

Cheers,

Dean
Kafayat Ullah Khan says:

Hey Seniors
I am facing the issue of Hyperlink or link. When I shared my files to another pc the link is not working?
Is there is any solution to one workbook hyper link to another workbook sheets?
Hans says:

I have a drop down box with several items in it, and when I click on a specific item, I am wanting it to recognize a number in the cell below it . What is the best way to do that?
dave says:

having troubles with cell reference's. Referencing a different sheet with ='Wine Data'!A6. no matter what
formatting I apply to either cell all I get is the formula displaying. Other cell references are working fine until I copy the formula and paste into another cell. Then the cell I copied will display the formula instead of the referenced cell.
- Hi,
  Perhaps the first character in this cell is not =, but a space or '
Tanvir says:

Hi,

How can I use the same range in different formulas withing selecting every time/copying it?

For Example:

=STDEV.P(D4535:D4562)

=MAX(D4535:D4562)

=MIN(D4535:D4562)

=AVERAGE(D4535:D4562)

I want to select the range for the first one only & want to see the rest of the formula's picked range automatically.
JJ Lee says:

Hello,
I'm trying to reference another workbook cell within box
Is this possible?

Here is what I'm trying to do.
='[https://app.box.com/s/4fcfdd2b25lyps2p46khzydj6maoejvv]Sheet1'!A1

Is it not possible to reference other workbooks from within box? Does it have to be a local file for this work?

Much appreciated!
- - JJ Lee says:
    
    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!
  - JJ says:
    
    Hi Alexander,
    Would you happen to know if my question is possible to accomplish?
    - Hi,
      The formula
      
      =HYPERLINK("[http://example.com/report/file.xls]Sheet1!A10", D1)
      
      retrieves the value from the cell A10 on Sheet1 from a file on the Internet. Hope this is what you need. Study carefully the articles I have recommended.
      - JJ says:
        
        Hi Alexander,
        So I went ahead and tried this formula you mentioned above. Just to test to see if it works, I set up the files as follows.
        
        Spreadsheet File1 = I entered the formula you mentioned
        Spreadsheet File2 = I entered a value -->50 in Sheet1 CellA10
        
        Upon completing this, it still does not work.
        The value I get per your formula = 0
        
        Any idea what the issue might be?
        
        Just to test to see if your
        
        JJ says:
        
        Just out of curiosity.
        Have you tried this yourself in Box? or are you trying this on a different platform?
        I'm wondering if this feature is not supported in Box.
        
        Hi,
        I hope you have studied the recommendations in the tutorial above.
        Pay attention to the following paragraph of the article above — Excel reference to another workbook.
        If you need to get a value from a file on the internet, here's an example:
        
        ='https://cdn.ablebits.com/excel-tutorials-examples/[count-unique-excel.xlsx]Unique values'!$A$3
        
        JJ Lee says:
        
        Thanks for the example URL you provided. I confirmed it works!
        
        So, here is the issue I have.
        The file on the internet I'm trying to access is on Box. and the URL for files on Box look like the following:
        https://app.box.com/s/4fcfdd2b25lyps2p46khzydj6maoejvv
        I made it public so you should be able to view the spreadsheet as well.
        
        Every time I try to use reference a cell from any worksheet on box, i get a reference error. I'm pretty sure its because of the URL of the file. Any further suggestions?
        
        JJ Lee says:
        
        hmm. i typed my response but its not showing.
        Let me try it again.
        
        Thanks for the example link above. I confirmed I can see the value when referenced.
        
        However, here is the issue I'm still having.
        The files I'm referencing aren't on a CDN. Its on Box.
        Therefore, the shared url of the file look like this - https://app.box.com/s/4fcfdd2b25lyps2p46khzydj6maoejvv
        I made the link public so you should be able to view it as well.
        
        Whenever I try your method using my URL from box, I get a reference error and I'm pretty sure its because of the URL.
        
        Do you have any further suggestions in trying to get around this issue?
        
        JJ Lee says:
        
        For some reason i still can't see my last response to you.
        Trying response without URL.
        
        Thanks for the example link above. I confirmed I can see the value when referenced.
        
        However, here is the issue I'm still having.
        The files I'm referencing aren't on a CDN. Its on Box.
        Therefore, the shared url of the file look like this - [Blank]
        I made the link public so you should be able to view it as well.
        
        Whenever I try your method using my URL from box, I get a reference error and I'm pretty sure its because of the URL.
        
        Do you have any further suggestions in trying to get around this issue?
        
        Hi,
        Unfortunately, I do not work with such files and with this CDN. So I can't help you.
        I don't think this will work as it is not an Excel file.
        
        JJ Lee says:
        
        Now that I know my comments wont show if I include a URL I copy pasted.
        Here is a workaround.
        
        h t t p s ://app.box.com/s/4fcfdd2b25lyps2p46khzydj6maoejvv
Bret Rios says:

I have a MASTER spreadsheet that uses cell references to pull data from three data template workbooks. All four of these files reside in the same folder on Dropbox. Periodically, I save a copy of each data template onto my Mac laptop and I add the day's date to the name. Unexpectedly, the MASTER spreadsheet unilaterally changes the cell references to the name of one of my data backup files, replacing the original cell reference that I had created within the MASTER. So then, that group of data is no longer being pulled from the Dropbox file. Why is this happening, and how do I prevent it?
- Hello!
  I think you created a copy of the file using Excel - Save as ... At this time you had your MASTER file open. When you save a file under a new name, all links to it are automatically changed.
Deepak says:

I have say 6 tabs in a excel workbook. the 6th tab shows the summary of the first 5 tabs total column, which is in a common cell say A10 in all 5 tabs. How do I now show the auto-generated summary of this total column cell A10 in 6th tab with having mentioned the name of all 5 tab names?
Caleb Hawn says:

Thanks for this! I'm taking a college course whose tutorials don't explain everything, so this helps a lot.
Jack says:

Is there a way to keep the file path to always read from the current folder that it is in?

For example say I have the excel file in file path \\trial\test1 and its reading info from the same path. Is there a way of copy and pasting that excel sheet into path \\trial\test2 and then reading from within the new folder ( \\trial\test2 ) without having to redo all the links?

Thanks in advance
- Hello!
  Unfortunately, Excel cannot search for files. You can change all links at once using the Excel tool - Find & Select - Replace
  - Jack says:
    
    Okay no problem, thankyou for your quick reply
Trusting Simukoko says:

How to break the links within the same workbook but different worksheet
Louis-David says:

hi my formula is
Here is the formula is my excel named: EXCEL-ONE
='[EXCEL-TWO.xlsm]Sheet1'!$B3

its looking up my second excel named: EXCEL-TWO,
in the Sheet1, and B3 cell

and it works flawlessly :D

my question:
i would like the Sheet1 word to come from a cell in EXCEL1
so i would have a blank cell in EXCEL1, that you could type in :
Sheet1, or Sheet2 or Sheet3 .... giving you knew the name of the tabs in SHEET2 obvisouly,

is that possible ?
- Hello!
  You can learn more about creating an Excel dynamic reference to another workbook with INDIRECT function in Excel in this article on our blog.
  I hope this will help, otherwise please do not hesitate to contact me anytime.
Priyanka Saxena says:

I want a copy of my excel workbook at other location and if i change one book that can reflect automatically other workbook can this happen please help
- Hi Priyanka,
  
  For this, you need to link every cell of the copy to the corresponding cell in the original workbook. It can be done with a formula like this:
  
  =[Book1]Sheet1!$A$1
  
  Where Book1 is the original workbook.
  
  The detailed steps to make such a reference are described in How to refer to another workbook.
Kareem says:

thanks alot
Nav says:

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.
- Hello!
  I recommend reading this guide about logging changes to Excel files. I hope this will help
  - Nav says:
    
    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.
terra says:

i am trying to drag my excel pattern but it isn't working as i want it to. it consists of a cell reference in cell A1, information in cells B1,A2,B2,andA3, and another cell reference in B3. i wanted to extend the pattern through cells A8 and B8. but when i extended it to A4 then the cell reference became cell A4 from the sheet it was coming from. is there a way to make it be cell A2 instead (and have it continue in cell A7 with reference cell A3 on the reference sheet)?
- Hello!
  I'm sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
  It'll help me understand it better and find a solution for you.
Peter says:

Good day! I've spent week trying to create a template where the prices are put in and calculated on one Excel file and are automatically synced with a Master Excel file (i.e. database), which records the calculated values on the prior worksheet, automatically updating itself when changes on the template occur.
Heather B says:

I have a workbook with a consolidated sheet and a sheet for each person. The consolidated sheet is more like a summary of each month and the total amount spent etc. the consolidated sheet pulls the totals, the name and the amounts form the sheets, but I wanted the sheets to pull the FTE and budget amount from the Consolidated sheet, this way when having to update the amount from year to year it can be updated in one place and not have to click on every sheet to update. The problem I am running into is when I try to sort the Consolidated sheet. I have used the =sheetname!A5 formula to pull the information onto each individual sheet but when I sort the consolidated sheet the cell stays the same even when the name moves. How do I get it to move with the sort?
- 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.
  
  It happens when the source file is not open in Excel. If you have opened the workbook which you refer to, there shouldn't be any problems and the data from that file can be used in your table.
Tony O'Brien says:

HI
I have a few issue's with my formula below retrieving information from another sheet.

{=INDEX('NSW RESULTS'!$B$6:$B$100000,MATCH(1,INDEX(('NSW RESULTS'!$A$6:$A$100000=$A$6)*('NSW RESULTS'!$B$6:$B$100000=MAX(IF('NSW RESULTS'!$A$6:$A$100000=A6,'NSW RESULTS'!$B$6:$B$100000))),0),0))}

1. This didn't return max (latest) date?
2. How to return second latest date?
3. How to place a second sheet? Both sheets are column matched so it's cheeking same information difference years. =INDEX('NSW RESULTS'!'NSW 19'!$B$6:$B$100000,MATCH(1,INDEX(( ????

Regards

Tony
- Hello Tony!
  Unfortunately, without seeing your data it is impossible to give you advice.
  Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.
  
  We'll look into your task and try to help.
  - Tony O'Brien says:
    
    Hi Alexander
    Have sent information through, hope you can help.
    
    Regards
    
    Tony
RONNIE C. DAYTO says:

iF I WANT TO GET A DATA FROM ANOTHER SHEET IN ONE COLUMN AND I WILL USE IT IN 6 DIFFERENT COLUMNS.
iT WILL BE POSSIBLE TO FIXED THE DATA IN 1ST,2ND,3RD COLUMN WHEN I CHANGED THE DATA FROM REFERENCE COLUMN?
I HOPE MY QUESTION EXPLAINED UNDERSTANDABLE.
THANKS IN ADVANCE
FROM THE PHILIPPINES
- Hello Ronnie,
  
  You may always disable the auto-update for your external links. Just go to Excel Options -> Advanced -> When calculating this workbook and uncheck the necessary options there.
  However, when you update your external links manually, they all get updated anyway. If you want to set a particular part of your external links, simply replace them with values using Copy - Paste Special - Values.
HIT says:

Dear Team,
I have some multiple excel files in folder. And i get data from this multiple files particular cell. Remember that i thought i can't open any close multiple files and receive data in one excel sheet.
At least particular cell value in data will get in close file to open file
- Hello!
  Please describe your problem in more detail. It'll help me understand it better and find a solution for you. Thank you.
Tony O'Brien says:

Help in retrieving information from another sheet.
Referencing sheet 1 QLD RESULTS FOR ALL INFORMATION
Formula sheet 3 QLD RESULTS
Sheet 1 column A Name / column B Dates / column C Distance / to column Z Time
Sheet 3 A6 Name reference with latest 4 dates

Formula's tried eg;
=VLOOKUP(A6,'QLD RESULTS'!A:A,1,FALSE)
=VLOOKUP(A6,'QLD RESULTS'!A:B,2,FALSE)
=VLOOKUP(A6,'QLD RESULTS'!A:C,3,FALSE)
=MAX(A6='QLD RESULTS'!$A$6:$A$20000,'QLD RESULTS'!$B$6:$B$20000,"")*FALSE

Regards
Tong
- Hello Tony!
  I'm sorry but your task is not entirely clear to me.
  For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what problem or error occurred. It'll help me understand it better and find a solution for you. Thank you.
  - Tony says:
    
    Hi Alexander
    
    Sorry about the misunderstanding before I hope below covers all queries.
    
    1 ; Find same source name on both sheets?
    2 ; Match source name with max, 2nd, 3rd, 4th latest date (4 Rows)?
    3 ; Match by name & date across all 10 columns?
    4 ; Formula "QLD FIELDS"
    5 ; Information from "QLD RESULTS"
    
    Retrieving information from "QLD RESULTS" & placing Formula in another sheet name "QLD FIELDS"
    Both sheets start in column A with name.
    "QLD RESULTS" has about 12 column of all information by eg;
    A=Name, B=Date, C=Distance, D=Track, E=Finish, & so on
    "QLD FIELDS" column A6 has "Source Name" to retrieve all information with latest 4 max dates.
    "QLD FIELDS" is formula area starting in Column N6 - N9 & across.(4 Rows latest 4 Dates)
    
    Say you have Jumping Joe as your "Source Name" in QLD FIELDS column A6!
    " QLD RESULTS" in column A find "Jumping Joe"/ in column B find latest max date/ match both A & B max date to get column C/ match both A & B max date to get column D/ & so on across. (QLD FIELDS column N6)
    Same as above to get 2nd max date information. (QLD FIELDS column N7, O7, P7, Q7 so on across)
    Same as above to get 3rd max date information. (QLD FIELDS column N8, O8, P8, Q8 so on across)
    Same as above to get 4th max date information. (QLD FIELDS column N9, O9, P9, Q9 so on across)
    
    Regards
    
    Tony
Tony says:

Hi
Require help in acquiring information from 3 difference excel's names with same 3 sheets names & all with same columns reference in A,B & C
In excel no1, Sheet 3 is where where I require the formula's in by name & date in column N6, reference name in column A6, in sheet 1 is where the information comes from (sheet 1, column A name , column B date, column C code, so on) ( down from top is Month,Day,Year eg; B6 1-01-2019 to B20000 4-20-2020 & adding)
Name appears multiple times in column A
Each Dates days appears 100 times
Formula eg; Column N6 Vlookup(A6,results!A:A,1,false)
Vlookup(A7,results!A:A,1,false)
Vlookup(A8,results!A:A,1,false)
VLookup(A9,results!A:A,1,false)

Column M6 Vlookup(A6,results!A:B,2,false)
Vlookup(A7,results!A:B,2,false)
Vlookup(A8,results!A:B,2,false)
VLookup(A9,results!A:B,2,false)
Require 10 names & there last 4 max dates, last 4 codes

SHEET 3
N6. M6. O6. P6
Name. Dates. Code. Distance.
6. Tony. Max
7. Tony. 2nd latest
8. Tony. 3rd latest
9. Tony. 4th latest

Same as above of 10 names
Retrieve information from 3 Excel States & place information in sheet 3 of I of 3 Excel
All by max date (Latest dates plus 2nd, 3rd & 4th latest)
Column width N6 to AE6 for information (A6 to L6
Regards

Tony
Andrew Aughton says:

I would like to use a cell value (1, 2, 3, etc) in the source sheet as a (looked-up) reference to another tab/worksheet (the target) in the same workbook (also named 1, 2, 3, etc), and thereby extract a value from a different cell in the target worksheet. I can't find any way of using the cell value in the source sheet to identify the relevant target tab in an EQUALS function such as =Sheet1!A2 (where the 1 is picked up from a cell in the source sheet). It seems straightforward but I've been at it over 8 hours....
Tim says:

How can I use a wildcard to reference a file name that changes?
John Kaure says:

I tried to use using Data Validation(list) Worksheet1 to go to Worksheet2 and Worksheet3 but I can not, please tell how to go about.
- Hello John!
  Data Validation (list) is used to populate cells with values. To move to another cell or another sheet you need to use VBA
Rode says:

I tried a =countif(source path, wb, sh, column, value) and it only works if i open the source file. How can I make it work without opening the source file.
- Hello Rode!
  If your source file doesn't contain any external references or macros, you can get the data from the file even if it is not open.
  Please check if you have the "Update links to other documents" option enabled in Options -> Advanced. When you open your file, you should get a message asking to update external references.
mcskou says:

Hi,
I wish to pull a Title from another tab, the title refers to a Number, that i put in automatic, next to the title cell.
No 2436(manual input -> Title (automatic pulled) - so the title cell needs to look in another tab, for the Number and then grab the title from cell next to it... understandable? :)

THANKS :)
- Hello!
  I suggest using a function VLOOKUP to pull data from another sheets. You can learn more about VLOOKUP in Excel in this article on our blog.
  Hope you'll find this information helpful.
Rosalie says:

I would like to be able to set this up:
If C2=F then C3=8.
I have this information listed in another sheet:
F 8 4
So I could just pull it from those cells. If C2=F then C3=8 and C4=4.
The problem I am running into is that I would like to have multiple options. For example: If I enter F into that cell, I would like 8 and 4 to show up. But if I enter R into that cell, I would like 6 and 3 to show up.

I hope I made that clear and that someone could assist me. Thank you in advance!
- Hello Rosalie!
  Hello
  If I understand your task correctly, the following formulas should work for you:
  in C3:
  =IF($C$2="F", Sheet2!C3, IF($C$2="R",6,""))
  in C4:
  =IF($C$2="F", Sheet2!C4, IF($C$2="R",3,""))
  I hope this will help, otherwise please do not hesitate to contact me anytime.
Rosalie Johnson says:

I would like to be able to set this up:
If C2=F then C3=8.
I have this information listed in another sheet:
F 8 4
So I could just pull it from those cells. If C2=F then C3=8 and C4=4.
The problem I am running into is that I would like to have multiple options. For example: If I enter F into that cell, I would like 8 and 4 to show up. But if I enter R into that cell, I would like 6 and 3 to show up.

I hope I made that clear and that someone could assist me. Thank you in advance!
robertgarber1 says:

I have 18 subgrantees (clients) and on one of my workbooks each client has their own tab/worksheet. Each of their worksheets have the same structure, and many of the cells populate from different workbooks on our network. Occasionally I want to send one of them a copy of their spreadsheet, but I have to send it as a .pdf because when I send their worksheet out of my network, the references all get lost.

Is there a way to copy a workbook and make whatever values are in a cell stay as they are, not as a reference?
Brendan says:

Hi All,

Please assist with a question of mine. I have it set up as Svetlana described and it works BUT doesn't automatically update between sheets when data is changed. If I want the main workbook (the one pulling data from other workbooks) to have the correct/current info...EVEN AFTER EVERY OTHER WORKBOOK IS SAVED...I have to double click on the cells then click the workbook its referencing from a file explorer type window. I want the data to auto-update on the main workbook as soon as the "source workbooks" are edited and saved b/c I have MANY lines of calculations being made and I don't want to have to double click on every one everytime I open the main file just to make data current.

Is there any way to solve this problem?
- Hello Brendan!
  First off, please check your Excel settings: Excel - File - Options - Advanced - Update links to other documents. Also, please go to Data->Edit Links and make sure the autoupdate for each link is enabled.
  
  Hope it'll help, otherwise please do not hesitate to contact me back.
  - Brendan says:
    
    Alexander,
    Thank you for replying, but bad news, I tried both and its still not working! Please see below for more info, as well as another question on something totally different (obviously you seem to be an excel expert, doesn't hurt to ask while I have you):
    
    - The Excel-file-options-advanced-update things was already checked. The data-edit links thing was checked as "automatic" not manual, so good there too.
    - Not sure if its helpful, but on that edit links area it gives options to the right (update values, change source, open source, break link and check status). The "Sources" listed to the left shows the correct filename (I've used "closedworkbook.xlsx" and "openworkbook.xlsx"), type: worksheet, Update: A, and Status: Unknown. When I click on Check Status the status for each instantly changes to Error: Source not found (which I think is wierd...both files are saved on my PC, both saved right next to each other on the desktop, have not been deleted, etc. When I click on Update Values it opens the file explorer type box for me to assign the file again, which I do, then the status changes to "OK". Then when I click check status again it goes back to error source not found.
    
    OK, second totally separate issue. I'm developing a somewhat elaborate spreadsheet (for me as a novice) that has formulas pulling data from other sheets within the same file. Please let me give you an example of what I'm trying to accomplish and then the issue (b/c it actually works fine until the issue):
    - Sheet 1 has the base data. There are 12 columns and 30 rows worth of it. One column (D) is "Jan" and has 4 separate peoples names, in no particular order (Ex: 1 Tom Smith, 3 Sue Jones, 6 Nick Thomas and 20 Lisa Brown). Tom, Sue, Nick, Sue, Lisa, Lisa, Lisa, Sue, Lisa, etc.
    - Sheets 2 through 5 are named Tom, Sue, Nick and Lisa, and I'm using a formula to auto-populate data from Sheet Jan, bringing over all of the data pertaining to that specific person. On Sheet Tom, the formula I'm using is =FILTER(Jan!A:L,Jan!D:D="Tom Smith"). Works PERFECTLY! Then when I email the spreadsheet to a colleague it changes the formula to =_xlfn._xlws.filter($Jan.A:L,$Jan.D:D="Tom Smith") and doesn't work. Tried it on different comps, different versions of excel, tried it on libre office, etc.
    - Hello Brendan!
      I have looked into the recommendations from Microsoft and made an experiment with my own files. I have found out, as stated by Microsoft support service (https://support.microsoft.com/en-us/help/925893/external-links-may-be-calculated-when-you-open-a-workbook-that-was-las), 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.
      
      It happens when the source file is not open in Excel. If you have opened the workbook which you refer to, there shouldn't be any problems and the data from that file can be used in your table.
      I hope it'll be helpful.
    - Hello Brendan!
      _xlfn normally shows up if an Excel workbook contains functions that do not exist in the version of Excel you work with. For example, there is the FILTER function in OFFICE365, but it doesn't exist in OFFICE2013.
      This function is currently available to Office 365 subscribers in the Monthly channel. It will be available to Office 365 subscribers in the Semi-Annual channel starting in July 2020.
      If there is anything else I can help you with, please let me know.
PTimlin says:

i use something like =IF(OR(A43="",C$42=""),"",INDIRECT("'" & A43 & "'!" &C$42))
This allows me to in the A43 to Type the tab name Say Tab1 Tab2 Tab3 (Spelled the same) and in the C42 spot point the cell i want data from C(Column) 42(Row)
This allows me to pull total from any page and any location. Without alot of Copy paste link
This all checks if the Sheet name exists returns an error if does not
lander says:

How to make cell to be a search engine??
with a thousands of data you want to search
Robert Clark says:

Hello helping,
I am not educated in Excel but always looking into it possibly me. I have an instance where I want to tell the sheet:
I have a drop down with 5 different module names(CMA1, CMA2,CMA3,CMA4, CMA5)
when I select one from the drop down, say if CMA2 is selected and displayed in A3 then I want C3 - C26 to display the information on the following tab (CMA Cell Sheet) that is in the workbook sheet K4 - K26. I have tried:
=IF(A3="CMA2",'CMA cell sheet'!K4:K26)
but I get a "VALUE" error pop up.
I would like to the same for CMA1, CMA3, CMA4, CMA5 but with different rows of information for each on the same CMA Cell sheet.
I hope that you can help me with this.
jo says:

how to copy particular one cell value to another sheet
Barbara says:

I have done it as described. It works across workbooks. However, the moment I make a change to workbook B, sheet B - and if it is only entering a value in any cell - and then save and close B.xlsx, I lose my value in A.xlsx/A-sheet-cell. The cell shows a #Bezug (in German) where the sheetname B should be. The rest of the link still shows correct, but the sheet-name got lost and was replaced by #Bezug.
What is going on????
jenzra says:

How do take the formula that brings over information from one worksheet to another.
So if I have Sheet2!A1 in sheet one in C3 then in D3 I want Sheet2!A19 so on and so forth. I want to find the formula I can drag over to each column so I don't have to go into the 4 separate work books and click every month to bring over my totals to combine everything. I tried some different things but can't seem to get the wording correct to get exactly what I need done.
- Bryan Jacobs says:
  
  That is the same issue I am having! I'd love to know how to do this..
- Joh Dow says:
  
  Me too. :(
Mr Bartaby says:

Is there a way to replace the filename with the value of another cell
Nicole Barnum says:

To increment the cell you'll need to use the CELL() function.
Dynamic Sheet: =INDIRECT(sheet&"!"&CELL("address",A1))
Dynamic Workbook: =INDIRECT("'["&workbook&"]"&sheet&"'!"&CELL("address",A1))

For me it looks like this: =INDIRECT("'["&$D$1&"]"&$B$1&"'!"&CELL("address",B6))
In D1 i have the file/workbook reference and in B1 I have the sheet name.
Aung Thiha says:

how to make the increment of sheet reference when drag down from different file .
My point is to auto update in one worksheet with different position of cell from another multiple sheets with fixed cell in arrangement of sheet.
dubravko says:

Hi,

I use several VLOOKUP with table in another workbook.
=VLOOKUP($E2;'C:\Users\daev\Desktop\Andreja\Rokovi\[Ispitni rokovi BAZA.xlsx]Sheet1'!$E$2:$V$4000;3;FALSE)

everything working good, but if I want to copy this two workbook to another PC it is not possible to work.
how can I write formulas to open workbook in folder they are saved.
- Kim Run says:
  
  You need to change "C:\Users\daev\Desktop\" that is location you file you should write follow you new location in the new PC