Consolidation Report - Existing Formula Not Capturing All The Data

[DDM64]

Good morning.

I have the following formula that I am currently using in my Consolidated Report (separate workbook - total of 4 workbooks involved here):

=IFERROR(('[WORKBOOK1.xlsm]Breakdowns'!$D$12+'[WORKBOOK2.xlsm]Breakdowns'!$D$12+'[WORKBOOK3.xlsm]Breakdowns'!$D$12),"-")

The current link values I am picking up are 0.8 in WORKBOOK1-Breakdowns; 0.0 in WORKBOOK2-Breakdowns; and 0.9 in WORKBOOK3. The result of the formula is "-" when it should be 1.7. I believe it is because there is a string "-" ( being used in formula. Not sure. However, my attempts to fix this have failed. Is there a Formula and/or Array Formula that I can use to solve this.

I want the formula to do error trapping for strings, #DIV!; #VALUE!; and #N/A.

Is this possible? Again, the above WORKBOOKS 1-3 consist of a lot of linked data within them and now I have to do a Consolidated Version off of them.

Thanks a bunch for your assistance. Deanna

[Pepe Le Mokko]

Please post a sample sheet with your data. If you leave out the IFERROR function, which error is returned?

[DDM64]

Hello Pepe Le Mokko,

I receive the #VALUE! error is I just use a simple SUM() function. I have gone back on all the sheets and took out the hyphen and leaving the quotes empty.

A consulting firm built this reporting system in excel for my company, so when I came aboard, I inherited the system and have been doing a lot of error trapping forumulas, etc. I cannot fix this one though and unfortunely, this system structure is huge and would I would have to send you many sheets, which is not feasible.

Basically, there are 3 workbooks that the consolidated workbook is pulling information from. Each of the 3 workbooks contain links from other source workbooks. I went back on the "source" workbooks and took out the hyphens there also (leaving an empty string). I hope this does not mean it is still reading as a "string" in a mathematical equation. I have to come up with a better formula. Might be why I did a simple sum function and the #VALUE!.

Anyhow, are you able to assist me? Deanna

[Pepe Le Mokko]

Are your workbooks open ?

[DDM64]

Yes. My apology for being so vague, data files are confidential. I also realize this type of reporting belongs in a Relational Database. I wil be doing this project in the future but they have asked that I make this work for now.

I hope you understand. Deanna

[DDM64]

Could someone please assist me with this

[DDM64]

Unsolved.

Could someone assist me with this. Deanna

[vlady]

be sure to check the "sources" for string values. you can transform them to "real numbers" using text to column.

[Pepe Le Mokko]

be sure to check the "sources" for string values. you can transform them to "real numbers" using text to column.

When you sum numbers, not using a function, it does not matter if the "numbers" are in fact text. The arithmetic operators will coerce hem as usual

[vlady]

@ pepe isn't that the other way around when you use function(sum) it will calculate but if not returns #value error

[QUOTEWhen you sum numbers, not using a function, it does not matter if the "numbers" are in fact text[/QUOTE]
A1 ->1
A2->2
A3-> C

=A1+A2 +A3 ->error
=sum(A1:A3) -> 3

since in post #1 the OP used the first one thus giving the error message and the suggestion was just to make sure computations are accurate we never know.

[Pepe Le Mokko]

Ah yes, I was talking about a "text number" or what should it be called?), not a real text string

[DDM64]

Good morning.

I understand what you mean and I appreciate the assistance from both of you.

Have a great day! Deanna