Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    funnction help required 'IF' (officexp 2002)

    i have a worksheet in which therw are 5 colums. in colum E:E i have what is an error mesage "#VALUE!" in sheet 2 named #Value i have placed a copy of the rows with errors and substituted the correct values.
    sheet 1 is automatically updated via a link but the errors have a low priority with our IT dept and will take months to review
    if i use the formula
    =sumif('#value'"A:A,sheet1!A14,'value!E:E)ican replace the errors withe the correct figures (A14 was the row in this case that the error wasb apparant)
    if i use this formula on the whole colum correct figure are changed to 0 and the errors corrected.
    iam trying to use =if(iserror(E4)....... with "=sumif('#value'"A:A,sheet1!A14,'value!E:E)" complete and utter failure being the result
    can any one help me with getting the formula correct

  2. #2
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: funnction help required 'IF' (officexp 2002)

    Its a little hard to follow your set-up from the description. Could you send a smaller version of the file with any private information removed. It appears that it would be a simple formula but without an example I am having a difficult time picturing what you need.

    yoyoPHIL

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: funnction help required 'IF' (officexp 2002)

    i'mn sorry about this but a copy of the file i sent from work hasn't fou nd its way to my pc at home , i will take a copy on a floppy or send from work

  4. #4
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: funnction help required 'IF' (officexp 2002)

    Alex

    Here is a stab at it. Sheet 1 has a formula in the E column which tries to run a simple calculation. Because some of the cell entries are text the result of the formula is #VALUE. I created this to mimic what you say is happening on your sheet. On the sheet called #Value the results from Sheet 1, column E are pulled into column A. In column B is a formula that looks at the results in A and if it does not find an error it keeps the result you have. Where it finds an error it places "sumif formula here". Because I don't understand your sumif formula, but you say it works, insert it into the formula in place of the "sumif formula here" statement adjusting for sheet names and ranges.

    Hope this helps.

    yoyoPHIL

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: funnction help required 'IF' (officexp 2002)

    it has a smell of success about it, look forward to tryng it tomorrow

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: funnction help required 'IF' (officexp 2002)

    sorry for the delay in replying, unfortunately i was unable to get your suggestion to work most likely because you had nothing to go off, other than my wording.
    here is the solution i eventually worked out (i've included a copy of the file for you
    1) copy route1 to route 2
    2) copy and paste the following in I4
    =sumif('#VALUE'!A:A,sheet1!A4,'#VALUE'E:E)
    3) H4 copy and paste
    =if(true=iserror(G4),I4,G4)
    4) copy H:H & paste special paste as values in route1
    it works though is still messy
    thank you for your in put

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: funnction help required 'IF' (officexp 2002)

    Two comments about your workbook.

    1- #Value is the error value that is returned when the wrong type of argument or operand is used in a formla. It is not a good idea to use this as the name of a worksheet. Not only might it confuse a user, but it can also possibly cause problems in a formula.

    2- On your #Value worksheet, in the range A61:A63, the AccNo have a space after the number. That means that these three values are stored as text and all of the other values in this column are stored as numeric values. This can cause problems with formula like the SUMIF that you are using.
    Legare Coleman

  8. #8
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: funnction help required 'IF' (officexp 2002)

    Alex;

    Here is an adjustment to your spreadsheet. I combined your formulas together and removed the redundant and uneeded columns. In G4 I placed

    =IF(ISERROR(E4)=TRUE,SUMIF('#VALUE'!A:A,Sheet1!A4, '#VALUE'!E:E),E4)

    and copied it down. Depending on how you get the results which are in column E (Route 1) you might be able to do this all in one column.

    yoyoPHIL

    Actually I don't believe you need to have the =TRUE statement. Remove it if you want.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: funnction help required 'IF' (officexp 2002)

    i thought by using "#VALUE" i would remember what i had done and why i needed the 2nd sheet also for othere people to follow what i have done, your comments are welcome and i will change the sheet name to something more appropiate (don't laugh) "ErrorValues"
    on your second point. this is a down load from a proteus system and is a pain as i have to download as a CSV , change any numerical/text type numbers by
    multiplying the noumber x 1--- any suggestions for a better result would be appreciated

  10. #10
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: funnction help required 'IF' (officexp 2002)

    it looks good i will try it out on tuesday with Legare Coleman suggestions

    thanking one and all for your input

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: funnction help required 'IF' (officexp 2002)

    If you pull in the same format all the time use a macro to import it. Use the OpenText method and set the FieldInfo parameter to ensure that all the columns are the format you desire (text, various date, skip, or general to have each defined)

    An easy way to get the code is it change the extension to txt and then start recording a macro
    Then open the file, change to delimited, comma
    and go thru the wizard.
    In each column change the type as desired
    stop macro and look at the code.

    It does not work if the extension is csv, because excel assume it is just a comma delimited and doesn't call the wizard.

    Steve

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: funnction help required 'IF' (officexp 2002)

    Running this macro on the sheet after the import might fix most of the problems:

    <pre>Public Sub FixData()
    Dim oCell As Range
    Application.ScreenUpdating = False
    For Each oCell In ActiveSheet.UsedRange
    If Not oCell.HasFormula And Not IsError(oCell) Then
    If IsNumeric(Trim(oCell.Value)) Then
    If Len(Trim(oCell.Value)) > 3 Then
    oCell.NumberFormat = "0000000"
    Else
    oCell.NumberFormat = "General"
    End If
    oCell.Value = Trim(oCell.Value)
    End If
    End If
    Next oCell
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

  13. #13
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    England
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: funnction help required 'IF' (officexp 2002)

    yoyophils function
    =IF(ISERROR(E4)=TRUE,SUMIF('#VALUE'!A:A,Sheet1!A4, '#VALUE'!E:E),E4) the function works like a dream, if you remove the TRUE from the function you only get TRUE or FALSE not the actual value
    to legare coleman, i would like to say thank you for your suggestion, on CSV values i hope to tryit out shortly and see what difference it makes to the working i have used to get round the problem in the past.
    than you to yoyophil and to legare for all the help they have give in resolving my queery

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •