funnction help required 'IF' (officexp 2002)

[alexanderd]

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

[yoyophil]

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

[alexanderd]

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

[yoyophil]

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

[alexanderd]

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

[alexanderd]

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

[LegareColeman]

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.

[yoyophil]

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.

[alexanderd]

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

[alexanderd]

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

thanking one and all for your input

[sdckapr]

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

[LegareColeman]

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>

[alexanderd]

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