funnction help required ‘IF’ (officexp 2002)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

thanking one and all for your input

Reply | Quote

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

thanking one and all for your input

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote