1. T

    Google Sheets: Referencing Data from 6 Sheets to One master

    Hi, Not super familiar with the platform, but trying my best. This instance is referring to google sheets, but excel and sheets seem to be pretty similar. I am trying to reference data from multiple sheets into a master summary sheet. I started with just referencing the sheets and the cell...
  2. C

    Mirror cells even when rows are deleted/inserted

    basically I have easy to read and modify values on sheet 1. Then on sheet 2 I have references to sheet1 (=sheet1!A1) in proper arrangement, with additional formatting mixed in. The problem is obvious when someone inserts, cuts, or deletes rows, on sheet1 you get a #ref error in sheet 2. I...
  3. L

    IFERROR formula

    Hi, Im working on a warehouse database with orders and quotes I have this IF formula =IF($B2="","",SUMIF(CheckOrderCommitted,B2, CommittedQuote)) But its giving me a $REF in all cells. This is because the cells reference too, Data is not in the cell., Im trying to do a IFERROR so that when...
  4. T


    I want to use the GET PIVOTDATA function to reference data fromwithin a Pivotable. When I enter: =GETPIVOTDATA("Amount",'2019'!$R$4,"Fund","1100","Orgn","2002","Acct","5815"),I get the result of $15,589.15 I want the function to be dynamic and retrieve the informationdisplayed in the Acct...
  5. N

    VBA PasteAll Question

    I'm trying to adjust a macro that was using .Select & Pasting pictures, but was throwing a fair amount of Run-time '1004' errors when trying to execute with an RPA tool. I know it's not the cleanest/most efficient, but I'm currently more concerned with the inconsistent results I'm getting. Some...
  6. JenniferMurphy

    how to use a named range in the Offset function

    In the table below, I have assigned the name "Balance" to column F. I would like to replace "F6" in I6 with the named range, "Balance", but the offset function doesn't like it. <tbody> R/C C D E F G H I J 4 Difference Formulas 5 Date Price Shares Balance Literal Relative Literal...
  7. C

    INDEX+MATCH: finds row+col but returns #ref (?)

    Hi, {=INDEX('190708'!H2:H3333;MATCH(1;('190708'!A2:A3333=Sheet1!A3)*('190708'!B2:B3333=Sheet1!B3);0);MATCH(Sheet1!H2;'190708'!2:2;0))} In this case, row 2 and column 8 in the chosen array, the value in that cell is 9 but excel returns #REF . What is wrong?
  8. D

    VBA Absolute Reference: #REF Errors

    Hello, I one last issue I am trying to solve to complete my project and I am at a loss on how to fix it. Sheet1 = "Inventory Value Report" - I am using this as an "import tab" where a new value report is pasted every time it needs to be used. My issue is that the first macro that runs...
  9. C

    Using the text from another cell to in a part of a array

    I have a table with some values that i want to sum according the year, name a store, and them according the current month... (I want to sum the accumulated values since the beginning of the month) I have a VLookup that gets me the text i want in the array: <colgroup><col width="221"...
  10. E

    How can I replace #REF with SOME in formulas?

    Hello, How can I replace #REF with SOME in formulas? Thanks!
  11. O

    Index Match question

    I suspect I need to use a different function but I am trying to lookup data and in one tab the city name is in one column, but the tab with the data the city names are spread out over manmy columns. <colgroup><col><col span="3"></colgroup><tbody> Location Revenue Expenses Boston 150000...
  12. I

    Delete row if #REF! shown

    Hi, I am using this simple code =DATABASE!A202 & son on. I have noticed that if nothing is in the cell in question then on the worksheet where there should be a value etc i see #REF ! Can we use some kind of code so that row is then deleted should #REF ! be in any cell in Column A Thanks
  13. B

    Help with using Offset for Row #REF error

    Hi Everyone and thank you in advance for your help. I have this complex (for me) formula on a large spreadsheet. As part of the process I must create copies of the spreadsheet with only the rows that meet "manager" as criteria. This means I'm often deleting the first row of data which then...
  14. B

    Vlookup with #ref! Error

    In column b from b6 to b11 i have some dates and in column c from c6 to c11 i have some amount. I have a date from column b in e6 and i use in f6 the formula =vlookup(e6,b6:b11,2,0) but the result it gives #ref ! How to avoid this? Please help.
  15. E

    How to make this neater?

    Hello, I have in Q118, this formula...
  16. W

    Macro delete ComboBox if LinkedCell = #REF!

    I need a macro that will go through my active sheet and delete all comboboxes that have a linked cell value of #REF !. Thank you in advance
  17. J

    Is it possible to Index a single cell array?

    I was trying to randomly select items from an array, I don't think the below is possible so I'm going to break it out into cells. I have an array in A1, everything below is submitted with ctrl shift enter just in case. A1={"test1","test2","test3"} I want to be able to...
  18. E

    Is there a way to avoid #Ref error if sheet is removed?

    Hi, is there a way to avoid the #REF error when the sheet that is being reference is removed? My formula is as follows: =IF(D1="mail","",IF(D1="carrier","",IF(I1="","",IF(H1="Active",IF(D1="","",IF(IFERROR(VLOOKUP(C1,BARK!F:F,1,FALSE),"")="","MISSING","")),""))))) So the BARK sheet is the one...
  19. H

    Soring creates #Ref - VERY UNIQUE PROBLEM

    I have used Excel for over 30 years and have never encountered this problem. When I sort, it tells me that I have a circulation issue (which I do not). It also says there could be a problem with WK1 (whatever the heck that is) Trying to read about WK1 was very limited and even mentioned...
  20. R

    Sumproduct formula for Ranking is returning #REF! Next to a SumProduct that is working perfectly.

    Hi all, <tbody> A C D E R S T 1 Paper Rank Div Rank 1 Div Rank 2 Score Diversity Score 1 Diversity Score 2 2 Andover 1 #REF ! #REF ! 80 80 #REF ! 3 Andover 2 #REF ! #REF ! 64 54 #REF ! 4 Andover 3 #REF ! #REF ! 62 62 #REF ! </tbody> In Column C, the formula is...

Some videos you may like

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