1. N

    #REF! error when opening 2 workbooks

    Hello, Workbook A picks up data from workbook B. However, each time I have both spreadsheets open, all my formulas in workbook A will spit out the error #REF ! Any idea how to stop this from happening? I've been avoiding opening both spreadsheets at the same time in the past but surely there...
  2. P

    Reading cell value from a closed Excel file error

    Hello Everyone, I have a workbook where i want to collect numeric data from another excel files (from only 1 cell) without opening them manually. I found a neat macro for this, but something is still not okay and i would like to ask for some help. In the main excel i have a unique string in...
  3. R

    Index match multiple criteria - can't understand why it doesn't work!

    Hi everyone, You would be doing me a big favour if you would explain why the following produces a #REF ! error. The formula is simply trying to find a match of Harry and Stan to return the number 13. <colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>...
  4. D

    Countif error

    Hi I got error for counit formula :COUNTIF(INDEX(DATA_Count,MATCH(B2,Data_Range,0)),">0") following table pl suggest correct formula regards devendra Sheet1 Data : <colgroup><col><col><col span="7"></colgroup><tbody> A B C D E F G H I Sr.No Date 1 01-Oct-17 113 1 5 0 18 0 1...
  5. J

    vlookup #REF error only when the returned value is a blank

    First time posting, thank you in advance for your responses. I have a vlookup to a separate workbook that works perfectly when the separate workbook is open. When the separate workbook is closed, I get #REF ! errors but only when the referenced cell is blank. Any idea what might be causing...
  6. M

    #REF when linked file opened

    I have a file that has links to a number of other spreadsheets. Sometimes when I have the file open and then open one of the linked files, the links for that file all change to #REF . Sometimes, it is the whole file path and sometimes it is the worksheet within the linked file that is replaced...
  7. D

    After linking, it come out #REF!

    After ive have been linking over 100 cells between 2 different excel files, all of a sudden, my links all went #REF! and I have to start over :( =VLOOKUP(D459;'[1. Speedo Cases GEM 170728.xlsx]speedo'!$B$10:$FZ$115;3;FALSE) became =VLOOKUP(D483;#REF!;3;FALSE) Any one has an idea how this...
  8. O

    Index Match match match? #REF

    I'm trying to look up values based on three criteria - my gut is that this needs to be an array formula but I'm beggared if I can get it to work. My source table looks as follows: Project Category 1 2 3 4 5 6 Project A Category A 9 4 3 6 0 2 Project A Category B 7...
  9. P

    How do I find "#REF" in how a function is actually written, not in what the cell displays?

    Greetings: First time on this forum. I'm glad this is here. Using 2003 on Windows 7 (I know, both are way behind the times, but they work for me!) I need to find a way to locate the source of a #REF error. Until now I've always been able to scan my columns and find the cell with #REF right...
  10. S

    Indirect sumproduct.

    Hi Hopefully someone can help I'm trying to use an indirect to a sheet name using Cell B5 then I want to look up how many cells are not blank in "A" using a sumproduct function. I've tried using the following when I go though step by step looks like its going to work but I end up with a #Ref...
  11. L

    I don't understand this behaviour

    I have this doubt that I would like to clear out. Probably a conceptual thing that I am not aware of. Lets say that I have a number 6 (or whatever) in B3. A number 5 in B6. In C3 this formula: =B3+2. It appears 8. Now delete the entry in B3 and drag the content of B6 to B3 (or just drag and...
  12. H

    Linking Data causes #Ref when opening upworkbook

    I Have a workbook that links to several workbooks in sub-folders When I open up the source workbook #ref appears in the destination workbook. How can I prevent this from happening ?
  13. C

    VLOOKUP and INDIRECT formula giving #REF error

    Hi, Hoping that someone can help me out. I've been tearing my hair out over this formula and feel like the answer is staring me in the face but I'm too close to see it. I am trying to have excel find the most recent time that a product arrived and enter that date into a cell. Each month of...
  14. T

    Make the vlookup function dynamic for different computers

    https://youtu.be/gCvlAtUIaZc I am doing exactly what is being done in the link above with video. The problem I have though is, this will only work on a specific computer because of the directory. What I need to do is transfer both the excel files to many computers. So when I do that and open...
  15. S

    Moving Column Data to another sheet using VBA

    Hello, I am moving data with VBA from 1 sheet to another and for the most part it works great. All the data is moved and is where i need it to be. The problem with one of the columns is that somehow the format is changed and the change causes my other reference to break. Below is the VBA i am...
  16. V

    Macro to select & clear (not delete) #REF errors.

    Hello, I feel like I am so close to this but I think I am not adept enough at macros to get it right enough to work. And, I have searched high and low and found everything but what I am looking for. I also tried to use the macro recorder but as I have learned it won't work for this. I know how...
  17. L

    Pivot data #REF error

    I'm currently pulling information from a pivot table using the following formula =GETPIVOTDATA("Account",'Debt Free'!$C$3,"Centre name","Charlestown","closedmonth","1","closedyear","2015") However I wish to change this to the following where the location is based upon two other cells...
  18. L

    Formula Help appears #ref

    Hi All, I am trying to avoid #ref appearing when I delete a row from the source spreadsheet. Does anyone have a solution. I want to avoid using Indirect function as the source spreadsheet needs to be open, for it to work. Any ideas, would be great!! The formulas are below, that I am trying...
  19. C

    OFFSET error

    I keep getting a #VALUE! or #REF! error with my offset formula. I've followed the logic and it should point to the desired cell. It is suppose to find the name in column A, go down 5 rows and return 2 columns/cells of data. =OFFSET(INDEX($A$1:$A$500,MATCH($I3,$A$1:$A$500,0)),5,,,2) can anyone...
  20. C

    Reference Error when opening additional workbooks

    Good morning all, First off I am using Excel 2013 When i open a workbook that has links to other workbooks everything updates as expected and is working fine and updates as expected. The formula is as follows: ='V:\Accounting\Regional\Q1 Forecast VS Plan\[1Q15 Forecast vs Plan...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back