1. M

    Index Match with multiple criteria formula error

    Hi, Please find here below as link for sample workbook, I am trying to get the output but getting #N/A and #Ref error in the report tab. Can you please help me on this. Thank you in advance for help. Regards https://drive.google.com/file/d/135npLmymBIEQlGQ3_jGq3piMi_iPzoYY/view?usp=sharing
  2. gheyman

    Index Match Match problem

    =INDEX(WBS_Dictionary!$E:$E,MATCH($A10,WBS_Dictionary!C:C,0),MATCH(C10,WBS_Dictionary!K:K,0)) If I take out either one of the matches from this formula I get an answer. But when I have both in the formula, like above, I get a #Ref ! Anyone know why?
  3. C

    REF error when deleting cells

    Error #REF ! Occurs when a formula references an invalid cell, most commonly when the cell is referenced by a formula that has been deleted or pasted. For example, in the image below, the Outcome column is referenced by the formula: = SUM (A2, B2, C2). If you delete the "Number 2" column...
  4. H

    References to external workbooks not working unless they are open

    Quick question regarding some references I have to external workbooks. The formula in question is: =VLOOKUP("*"&B5,INDIRECT('Links to Workbooks'!$I$3),'Links to Workbooks'!$C$4,FALSE) Where the lookup value B5 contains "Total Revenue" and is what I am hoping to match using VLOOKUP in other...
  5. N

    Extracting selected data from PivotTable and placing it into the table?

    Sorry for disturbing you guys, but I could use some help with extracting some data from the pivot table and placing it into the table As you can see there is a pivot table on the right. There will be only 2 types of tester but it is dynamic range as I do not know which tester will be use every...
  6. F

    Links to other workbooks returns #Ref when spreadsheet is closed

    Hi, I have a strange situation. I have a formula in a spreadsheet that links to another: ='I:\[A.xls]Sheet1'!C100 For some reason the following is occurring: Whilst 'I:\[A.xls]Sheet1'!C100 is closed If I highlight the 'I:\[A.xls]Sheet1'!C100 and press...
  7. Z

    #REF location ? POssible

    HI i have on sheet1 the whole sheet full of alot of stuff. Going past 5000 lines. Is there a formula i would prefer or even a macro I can put on sheet2 to locate on sheet1 where any cells that have #Ref . It takes forever to find thanks
  8. W

    VBA code to find/replace #ref in formula

    I have searched high and low. Is there a way to find several #REF ! in a formula string within a range (B11:AP55) in a single worksheet and replace it with a "Master". =SUMIFS(INDEX(#REF !,0,MATCH('SITE'!AP$3,#REF !,0)),#REF !,'SITE'!$A11,#REF !,'SITE'!$A$45)
  9. K

    Data not staying in sheet #REF!

    I am running Windows 7 and Excel 2007. I have data on one sheet (mm-dd-yyyy Emergency Log) that is copied to another sheet (mm-dd-yyyy Log Summary) for a summary that gets sent out to several people. When it is created it works fine but when the files are closed and the summary is reopened all I...
  10. J

    Odd vlookup behavior

    I have named range called REMOVE. REMOVE is 24 Columns wide When I enter <tbody> =VLOOKUP(A2,REMOVE,20,0), i get a valid response. When enter <tbody> =VLOOKUP(A2,REMOVE,24,0) I get #N/A Error. ??? I can understand a #REF errror, but how can i get #N/A by simply changing the index/Row...
  11. M

    Copy values from pivot table to another sheet's empty cells - Macro

    Hi! I have this table on sheet "May 2018": <colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <col style="width:48pt" width="64" span="27"> </colgroup><tbody> DAY DATE Sales Sales Change % Item...
  12. H

    #Ref Error

    I have a vlookup combined with an index Function. If I open up the workbook from where the formula looks up the values, I get a #Ref error in the formula in the destination workbook =IFERROR(IF(VLOOKUP($B370&"",'[BR1March.xls]#Ref...
  13. S

    FormulaR1C1 and #REF!

    I am receiving the #REF ! error in cells on a sheet generated when applying the following code into a vba sub. When I view the formula in the sheet the #REF ! symbol is in the first R1C1 cell referenced (R[-23]C[-7]) and (R[-69]C[-7]) Range("L6").value = "dprice1" Range("L7").FormulaR1C1 =...
  14. K

    #REF! error tyring to get data from another wb using Indirect formula

    In my wb1/sheet1, I got this cells: A21 = Full name of wb I'm trying to get data from ("Test1.xlsx") B9 = Name of sheet in wb Test1.xlsx ("Eget materiell") B10 = Name of cell reference wb Test1.xlsx ("B2") If I put in this formula in a cell in wb1/sheet1: ='[Test1.xlsx]Eget materiell'!$B$2...
  15. S

    Index Match Error #REF

    Hi, I am apply the following index match formula in G3 but I get a error #ref . G3=INDEX($D$3:$G$12,MATCH(J$2,$D$2:$G$2,0),MATCH($I3,$B$3:$B$12,0)) I would appreciate if anyone can give me some guidance. Data range A2:G12 and Desired result range I2:M5 <colgroup><col><col><col><col...
  16. matthewlouis

    Lookup Conditional Formula with Multiple Columns

    <!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/>...
  17. J

    deleting data gives a #ref

    I have an excel 2007 file which over the years has grown to ~ 20 yrs of input data -- which is unnecessary now so I deleted about 5+ years of input data ( dates & numbers) and assumed that Excel would restart from the oldest number now not true -- all output columns are filled with #ref...
  18. J

    Problem with INDEX/MATCH It work for first 3 row only

    Hi I have a problem with INDEX/MATCH formula. I have many scenarios writen in second sheet named 'source'. Single scenario has 3 rows with same number of scenario. Column A and B are source for this index/match formula this formula i have in C2 This formula is also in whole C column...
  19. S

    create an alert or pop-up window when #NUM! or #REF! appears in a cell

    Good day folks As the title implies, I want to create a noticeable alert for the user if #NUM ! or #REF ! appears even once within a range of cells occupying one column ( ie column BX, Rows 10 to 1000.) Any takers? :)
  20. G

    Macro to remove Rows that are 0, #REF!

    Hello, I am trying to create a macro that will actually remove all Rows where cell A1 has a value of 0, #REF !.

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