#value

  1. A

    Get rid of #value when numbers removed from spreadsheet

    Hi I have the following formulae in a cell related to a worksheet. =IF($E29="Yes",0,IF(EC29=0,0,(IF($FE$19=0,EC29,EC29+EC29/$FE$20*$FE$19)))) When I remove the 0 from the cells its looking at in column FE it returns a #value. How can I get rid of this and allow cells in FH to be blank and...
  2. D

    Help with COUNTIFS for a relative newbie

    Hi I need to count the number of elements in two different columns and would like to use countifs. If I use countif with the required individual criteria, I get valid results, when I combine them using COUNTIFS then I get a #VALUE! and no result. One of the columns contains business function...
  3. M

    Sumproduct - Returning #value

    Hi, As I deal with lots of data with multiple identfiers in my cost models I generally use Sumproduct and have done for some time. I have an issue though which i think I have identified as one of the labels in the Sumproduct formula that returns the #value. Formula is...
  4. C

    Macros Formula Creates Error in Entire Column

    Hello. So I've been stuck with this problem for quite some time now. I created a formula using macros and applied it to an entire column in my spreadsheet. However, once I edit the value of some other cell the macros references to OR delete a row anywhere in the sheet, all the cells that use...
  5. S

    VBA replacing a formula with a macro returns #Value (Wrong data type)

    I have an excel macro that copies a tab from the previous month, renames it to the correct month, and replaces the formulas with the correct month/formula. The issue I ran into is that when it replaces the formula with the correct month, it returns #Value and says "wrong data type". I believe...
  6. L

    INDIRECT() randomly breaks with ROW()

    I am trying to do dynamic formulae creation and have successfully done in the past, but I am facing a very strange issue whereby using the ROW() formula within the INDIRECT() formula breaks it and it gives a #VALUE error. If I enter 3 values in column A as below: A1: 1 A2: 2 A3: 3 I can then...
  7. J

    Tracking down #value error in Sumproduct result

    Greetings all! I am working on a dynamic reference for our accountant to allow him to get cost data on different items being produced. I have a database that has production data entered for each day and I am trying to pull the data for various time frames based on a =NOW() value in A1. I am...
  8. B

    INDIRECT() returns #VALUE error and I don't know why!

    I'm trying to use the INDIRECT function in combination with the INDEX MATCH functions to look up certain values associated with different baseball players' names (if my profile name didn't give that away!). I've developed the following formula... =INDEX('Batter Universe'!D:D,MATCH('Player...
  9. R

    Using offset in an array function

    My data is laid out in a large grid (194R X 50C) E6:BB6 is top row and E199:BB199 is bottom row. I want to check if some element is in each row of the grid and create an arrray of 1s and 0s. This is to multiply with another array of 1s and 0s that I already have so I can sum up the result...
  10. P

    UDF/Custom Function Working 1st Time, but Recalculating 2nd Time and Returning #VALUE... ??

    Hello VBA and UDF world, I have a UDF/Custom Function that works the 1st Time it's ran, but when I try to use the data in a pivot table the function attempts to recalculate and returns #VALUE. I have researched this issue, came to some conclusions, but can't figure it out entirely... I found...
  11. J

    Making a list:

    I found this nifty formula for making a list: http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx However, the very first step is throwing me off by returning a #VALUE error. Why do I get this error when I...
  12. J

    #VALUE - no apparent reason i can find

    hello...first post! :) using idex function with nested IF and having this returned and cant think my way around it..any suggestions would be greatly appreciated! formula: =INDEX($C$2:$C$7,SMALL(IF($B$9=$B$2:$B$7,ROW($B$2:$B$7)-MIN(ROW($B$2:$B$7))+1,""),ROW(A1))) data looks like: <TABLE...
  13. S

    Get rid of #VALUE error message

    I have a simple formula =IFERROR(VLOOKUP(A42,'Sales Pricing.rdl'!$B$5:$G$1936,6,0),F42*0.8) which I am using to create a price list. However, there are blank rows which creates the error #VALUE. What do I need to add into this formula to prevent the error message appearing? I am using...
  14. M

    Excel VBA using GetPivotdata is returning #VALUE

    Hi Experts<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p> I have setting up a customer Excel function in VBA that gets data from a Pivot table.<o:p></o:p> The purpose of the function is to return the value from a pivot table like Excel GETPIVOTDATA...
  15. L

    Stop a cell displaying Value!

    I have the formula =D19*(1+'Results Other'!$G$2) in a cell to increase a the value in D19 by a percentage. However if there is not a value in cell D19 it gives the result #VALUE! how do i get it to display a blank box also
  16. N

    #Value or #DIV/0! Error

    I want to count the number students (Column B) that completed a task (Column C). But I only want to count the student one time. I also need to be able to account for blank/null cells because I am not sure how many entries will be in my sheet. Here is a data: Student 1 Yes Student 2 Yes...
  17. S

    List formulas mysteriously going to #VALUE...

    ... but if I put the cursor in the top of a column and then put the cursor at the beginning of the formula and press ENTER I am asked if I want the formula to be copied down the column. After this, everything is as expected. What causes this? How can I prevent it? The formulas are right...
  18. S

    #Value resulting from formula that traps it??

    Below is column C. It is being evaluated by the formula: =IF((ISERROR($C4)),"",IF($C4="Deposit",$C4,IF(OR($C4="Check",$C4="Debit Card"),$C4*-1,""))) Yet, the result shows #VALUE; which I thought would have been taken care of by the ISERROR function? What am I missing???? ******...
  19. L

    Display "" without causing #VALUE in next formula

    A1 Date (user entered value) B1 =Text(A1,"ddd") C1 Arrive Time (user entered value) D1 Dept Time (user entered value) E1 =If(IsBlank(C1),"",Mod(D1-C1,1)*24) F1 Afterhours Home Work (user entered value) G1 =E1+F1 Problem: If 'C1' is blank, then value "" placed in E1 -- which causes...

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top