#ref! error

Hi everyone I have created a proforma invoicing system in Excel for our landfill site. I have a sumif formula that calculates the total weight of each type of waste (General, mixed waste & large rubble) and then it gets multiplied by the tariff. This is on the "Region 1" sheet. Companies are...

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...

HI can someone help. i have a formalu below =IFERROR(INDEX('sheet2'!$A$5:$S$32,MATCH('sheet1'!B19,'sheet2'!$A$5:$A$17,0),5),"") however when i need to add/dleete some columns in sheet2 and but i want it to stay and look at the above, but when i add/delete the columns the cells it is...

I am not sure what is causing below error, it seems my mind has stopped thinking ... it's been a long day. Wrapping below FORMULA in a SUM function gives me #REF error in excel. The formula with SUM is in cell E4. INDEX(A:C,ROW()) The column reference is omitted as I want it to return all 4...

Hello, I am trying to do a lookup based on a variable file name which I'd like to fetch from cell I2. I am very new to complex excel formula so am hoping someone can tell me exactly what I need to do to make this work! I've tried looking around other peoples posts on this and adapt it but get...

Hi I have 2 source workbooks being created by a system, alongside 2 templates, A and B. A reads directly from the source file 1, with some additional basic calculations. B reads some data from the source file 2 and the results of the calcs in A. My issue comes with Workbook A, which has 3 tabs...

I am having issue with a dynamic named range that is changing its reference if I delete a row from it using VBA. The Named range is: AllJobsData =OFFSET(Data!$A$7,0,0,COUNTA(Data!$C$7:$C$1048576),7) The VBA code below is designed to go through each line of AllJobsData, bottom up, and delete...

When I filter my data for a certain column, I receive a #REF ! error randomly for one of my columns (Column C). I use other formulas within the other columns and they sort fine. Column C uses the INT function to calculate one's age as shown below: To calculate Column C (Age), the formula used...

*** UPDATE *** My bad, I didn't realize the array was wrong (i column only!)! :crash: Spent with this about an hour! Can't find how to delete a post neither. SORRY. :rofl: Hi all, I got some great help from this forum previously, so I thought I'll return. I am trying to do a simple VLOOKUP...

Hi, I have a pivot table whose source data is a powerpivot model. When I try and reference a cell from the pivot table in another formula I get a #REF! error. As an example the pivot table is as follows: and the formula that has the #REF! result is: =+GETPIVOTDATA("[Measures].[Sum of...

Good Evening, I am trying to complete a vlookup where the lookup value (B3) is a text cell that is updated from another database and thus changes. The vlookup in located in the same workbook as the the (B3) reference and the table array and column index refers to another workbook. Trying to add...

Hi all, I have an Excel 2010 workbook in which I am using a Workbook_BeforeClose event to do a number of things. The part I am having trouble with basically is designed to remove all existing named ranges, then recreate them based on the rules in the code. The reason for this is because users...

Hi, i read and googled about this topic and still i'm not clear yet nesting formula syntax and usage of INDIRECT with some others formulas using multiples range and/o criterias. For example : I need to have cell reference using INDIRECT with SUMPRODUCT after added my result cell display #REF...

My Workbook starts off with three sheets. On the first is my summary sheet (named Summary), the second is my master sheet (named Master 0) that has a button to create subsequent sheets, the third sheet is the first of the sheets that I need to show up on my summary sheet (named Day 1). I need...

Hi experts I have a set of velocity speeds for soils and rocks and I'm using an index and match formula for these speeds for data I'm importing. The formula is =INDEX(H28:H45,SUMPRODUCT(--(I28:I45<=D7)*(J28:J45>=D7),ROW(I28:I45))-ROW(28:28)+1) The reference table H28-J45 is shown below...

Hi All, I have the below code which looks to see if a certain cell contains a phrase, either bearing or figure. However since this cell is a linked value there is a chance that an error (#Ref!) will show instead. I thought my code would search for the error and if it did not exist would move...

Random #REF! Error We use a large Excel file with over 1,000 external links located on one of the worksheets called the "Data Table". The external links point to other Excel files which are located on various servers. The Data Table external links work correctly most of the time. However...