IF ISERROR INDIRECT MATCH Formula Question???

G

grateful

=IF(ISERROR((INDIRECT("'August 31, 2008'!H"&MATCH(C4,'August 31,
2008'!C:C,FALSE)) + (INDIRECT("'August 28, 2009'!H"&MATCH(C4,'August 28,
2009'!C:C,FALSE)) + H4))/(INDIRECT("'August 31, 2008'!J"&MATCH(C4,'August 31,
2008'!C:C,FALSE)) + (INDIRECT("'August 28, 2009'!J"&MATCH(C4,'August 28,
2009'!C:C,FALSE)) +J4))),IF(L4>0.01,L4,""),((INDIRECT("'August 31,
2008'!H"&MATCH(C4,'August 31, 2008'!C:C,FALSE)) + (INDIRECT("'August 28,
2009'!J"&MATCH(C4,'August 28, 2009'!C:C,FALSE)) + H4))/(INDIRECT("'August 31,
2008'!J"&MATCH(C4,'August 31, 2008'!C:C,FALSE))+ (INDIRECT("'August 28,
2009'!J"&MATCH(C4,'August 28, 2009'!C:C,FALSE)) +J4))))


Hello!

I am working on this formula above...it's giving me a response...but it's
not the write answer. Can anyone help me find what's wrong?

I'll try and explain my figures:
On my worksheets I have project names in column C. If the worksheets dated
August 31, 2008 and August 28, 2009 have a project that matches a project in
Column C on my current page...I need to pull some of that info over into my
formula.

On all three sheets I take column H and divide it by column J. I need to
add up the 3 figures in matching projects from H on the three sheets, and
match up the 3 figures in matching projects from J on the three sheets, and
divided the sum of the 3 H's by the sum of the 3 J's and put the answer in
column N of my current sheet.


This is what I have:

H J
Current : 36.40 0.00
August 28, 2009: 39,433.17 108.80
August 31, 2008: 6,269.75 30.00

The formula above is returning 46.10; but it should be 329.53

Can anybody see what I'm doing wrong?

Thank you so much for even looking.
 
T

T. Valko

Hmmm...

Is the project name on both sheets?

It looks like you should be able to use a form of SUMIF to do this.
 
G

grateful

Ok, I've been working on it, and now the only thing I can't get to work is
the ISERROR.

Here is my formula now:

=IF(ISERROR(INDIRECT("'August 31, 2008'!H"&MATCH(C4,'August 31,
2008'!C:C,FALSE))+H4+INDIRECT("'August 28, 2009'!H"&MATCH(C4,'August 28,
2009'!C:C,FALSE)))/(INDIRECT("'August 31, 2008'!J"&MATCH(C4,'August 31,
2008'!C:C,FALSE))+J4+INDIRECT("'August 28, 2009'!J"&MATCH(C4,'August 28,
2009'!C:C,FALSE))),IF(L4>0.01,L4,""),(INDIRECT("'August 31,
2008'!H"&MATCH(C4,'August 31, 2008'!C:C,FALSE))+H4+INDIRECT("'August 28,
2009'!H"&MATCH(C4,'August 28, 2009'!C:C,FALSE)))/(INDIRECT("'August 31,
2008'!J"&MATCH(C4,'August 31, 2008'!C:C,FALSE))+J4+INDIRECT("'August 28,
2009'!J"&MATCH(C4,'August 28, 2009'!C:C,FALSE))))

Any suggestions?
 
G

grateful

Hi,

Yes the Project name is possibly on all three sheets, but not necessarily.
It could be on just the current one, or on just the current one and one of
the other sheets. Not necessarily all three.

I posted a formula below that I actually got working...except the ISERROR
isn't working, so on some of the cells I get an #N/A

Not sure what I've done wrong....
 
T

T. Valko

What do want as a result if ISERROR is TRUE?

You have ISERROR terminated at the wrong place.

If a match of the project name isn't found or, if the sum of J is 0 then
you'll get an error, either #N/A or #DIV/0!.

Another problem I see is this:

IF(L4>0.01,L4,"")

What precedence does that have?
 
G

grateful

Hi, I'm so sorry...this formula works...except if one of the other sheets
doesn't have the project. Example, if August 31, 2008 doesn't have the same
project, instead of calculating it now with just the information it does
have, it comes back #N/A

I'm sorry I'm so lost.

This is what I have:

=IF(ISERROR(INDIRECT("'August 31, 2008'!H"&MATCH(C7,'August 31,
2008'!C:C,FALSE))+H7+INDIRECT("'August 28, 2009'!H"&MATCH(C7,'August 28,
2009'!C:C,FALSE)))/(INDIRECT("'August 31, 2008'!J"&MATCH(C7,'August 31,
2008'!C:C,FALSE))+J7+INDIRECT("'August 28, 2009'!J"&MATCH(C7,'August 28,
2009'!C:C,FALSE))),IF(L7>0.01,L7,""),(INDIRECT("'August 31,
2008'!H"&MATCH(C7,'August 31, 2008'!C:C,FALSE))+H7+INDIRECT("'August 28,
2009'!H"&MATCH(C7,'August 28, 2009'!C:C,FALSE)))/(INDIRECT("'August 31,
2008'!J"&MATCH(C7,'August 31, 2008'!C:C,FALSE))+J7+INDIRECT("'August 28,
2009'!J"&MATCH(C7,'August 28, 2009'!C:C,FALSE))))
 
G

grateful

"I Think"....IF(L4>0.01,L4,"") had something to do with my original "IF"
formula. I think I wanted " " to appear instead of #N/A or #DIV/0!

For example...If the same project isn't in August 28, 2009 and August 31,
2009, and is ONLY in the Current...then I want the figure in L to appear also
in Row N.

Can you help me with the following:
1) Where should I terminate the ISERROR? I can't find it.
2) If the project is only in the Current worksheet, how do I have L4 appear
in N4 for the project in C4?
3)If #N/A appears or any other error message, I would like the cell to just
be left blank.

Thank you so much!
 
T

T. Valko

Let's start with this and go from there:

=(SUMIF('August 31, 2008'!C:C,Sheet1!C4,'August 31, 2008'!H:H)+SUMIF('August
28, 2009'!C:C,Sheet1!C4,'August 28, 2009'!H:H)+H4)/(SUMIF('August 31,
2008'!C:C,Sheet1!C4,'August 31, 2008'!J:J)+SUMIF('August 28,
2009'!C:C,Sheet1!C4,'August 28, 2009'!J:J)+J4)

Do you get a correct result with that?
 
G

grateful

HI,

No I'm sorry I don't get anything with it. It comes back #VALUE!

This is the formula I tried:
=(SUMIF('August 31, 2008'!C:C,Current!C4,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C4,'August 28,
2009'!H:H)+H4)/(SUMIF('August 31, 2008'!C:C,Current!C4,'August 31,
2008'!J:J)+SUMIF('[August 28, 2009]August 28, 2009'!C:C,Current!C4,'August
28, 2009'!J:J)+J4)

My "Current" I believe is your "Sheet1"

I'll keep looking at it and let you know.

Thank you.
 
G

grateful

Ok, sorry YES, it works! I did something wrong the first time.

This is my formula:

=(SUMIF('August 31, 2008'!C:C,Current!C7,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C7,'August 28,
2009'!H:H)+H7)/(SUMIF('August 31, 2008'!C:C,Current!C7,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C7,'August 28, 2009'!J:J)+J7)

1 )Do you have any suggestions for how to show the cell blank, instead of
#VALUE! ? Because for instance C8 is a blank cell, there is no project,
but if I drag the formula down, I get #VALUE!

2) If you can, could you explain how the formula above works, so I can
understand it?

Thank you so much!
 
G

grateful

LOL. Well I think I figured out how to get the #VALUE! to disappear with
this formula:

=IF(ISERROR((SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!H:H)+H9)/(SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!J:J)+J9)),(""),((SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!H:H)+H9)/(SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28, 2009'!J:J)+J9)))


If you have time to breakdown the SUMIF formula that would be so helpful for
my understanding, but if you don't, that's fine. You have been very helpful,
my sheet works, and I can move forward.

Thank you SO much! I am extremely grateful!
 
T

T. Valko

Ok, we're making progress!

I don't see how you're getting a #VALUE! error. The only thing that would
generate a #VALUE! error is if the cells in H and/or J on the Current sheet
contain TEXT. In this portion of the formula:

......)+H9)+.....)+J9

If a project doesn't exist the formula should simply return 0. The only
other thing that should generate an error is if the denominator of the
formula evaluates to 0. For example: =10/0 would return the #DIV/0! error.
That would only happen if the project name isn't found on the "date" sheets
or if the project name is found on the date sheets but the sum from those
sheets is 0.

We can handle both of these situations but of course this will make the
formula pretty long.

Instead of error trapping the entire formula we only need to test the
denomiator for 0:

..../(SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!J:J)+J9))


=IF(SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!J:J)+J9=0,"",(SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!H:H)+H9)/(SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!J:J)+J9))

That will take care of any #DIV/0! errors. Now all we need to do is figure
out why you're getting #VALUE! (see above). Let's get the formula working
100% of the time then I'll explain how it works.
 
G

grateful

Hi, I'm sorry I think I'm confusing things with all my messages.

I found this formula works 100%:

=IF(ISERROR((SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!H:H)+H9)/(SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!J:J)+J9)),(""),((SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!H:H)+H9)/(SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!J:J)+J9)))


If I look at the formula you last gave me...I can't get it to work for some
reason, but I'm not sure I really have a problem with the denominator being
0. My problem comes from some of the rows don't have a Project Name in the
"Current" worksheet. So when I was dragging the formula down all of column
N...it was giving me the #VALUE

I'm not sure why, but if I combine the =IF(ISERROR(FORMULA),"",(FORMULA), it
works perfectly.

Are you still able to explain the SUMIF to me? Or have I confused things
too much? :blush:)

Sincerley
 
T

T. Valko

Ok, if you have a formula that works let's just leave it at that.

SUMIF works like this:

......A.....B
1...x......2
2...y......1
3...x......1
4...x......3
4...z......2

=SUMIF(A1:A5,"x",B1:B5)

If A1:A5 equals x sum the corresponding cells in B1:B5

A1, A3 and A4 equal x so sum B1, B3 and B4

Result = 2+1+3 = 6

So, in your formula we're doing the same thing but in this case we're
summing based on the project name.

SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!H:H)

Since there are multiple sheets we need a SUMIF for each sheet:

SUMIF('August 31, 2008'!C:C,Current!C9,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C9,'August 28,
2009'!H:H)

That makes up the numerator of the calculation then we do the same thing for
column J on each sheet to get the denominator. These separate results are
then divided for the final result.
 
G

grateful

Yeah! I understand! Thank you so much!

I have another question...I don't know if you can help me with this...it's
on a different line of thought.

Each week I start a new sheet and name it "Current", and change the previous
week's sheet name from "Current" to that weeks date, (ex. September 10, 2009).
Then I copy columns I to N from September 10, 2009 to sheet "Current".

When I do this, the formula in N no longer says "Current" like this
:=IF(ISERROR((SUMIF('August 31, 2008'!C:C,Current!C7,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C7,'August 28,
2009'!H:H)+H7)/(SUMIF('August 31, 2008'!C:C,Current!C7,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C7,'August 28,
2009'!J:J)+J7)),"",((SUMIF('August 31, 2008'!C:C,Current!C7,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C7,'August 28,
2009'!H:H)+H7)/(SUMIF('August 31, 2008'!C:C,Current!C7,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C7,'August 28, 2009'!J:J)+J7)))

instead it comes with the date: September 10, 2009 like
this:=IF(ISERROR((SUMIF('August 31, 2008'!C:C,September 10, 2009!C7,'August
31, 2008'!H:H)+SUMIF('August 28, 2009'!C:C,September 10, 2009,'August 28,
2009'!H:H)+H7)/(SUMIF('August 31, 2008'!C:C,September 10, 2009!C7,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,September 10, 2009!C7,'August 28,
2009'!J:J)+J7)),"",((SUMIF('August 31, 2008'!C:C,September 10,
2009!C7,'August 31, 2008'!H:H)+SUMIF('August 28, 2009'!C:C,September 10,
2009!C7,'August 28, 2009'!H:H)+H7)/(SUMIF('August 31, 2008'!C:C,September 10,
2009!C7,'August 31, 2008'!J:J)+SUMIF('August 28, 2009'!C:C,September 10,
2009!C7,'August 28, 2009'!J:J)+J7)))

Do you have any suggestions of how to copy the columns to the new sheet,
without the formula changing when I bring it over? So it always just copies
with the sheet name "Current"?

Thank you.
 
G

Glenn

grateful said:
Yeah! I understand! Thank you so much!

I have another question...I don't know if you can help me with this...it's
on a different line of thought.

Each week I start a new sheet and name it "Current", and change the previous
week's sheet name from "Current" to that weeks date, (ex. September 10, 2009).
Then I copy columns I to N from September 10, 2009 to sheet "Current".

When I do this, the formula in N no longer says "Current" like this
:=IF(ISERROR((SUMIF('August 31, 2008'!C:C,Current!C7,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C7,'August 28,
2009'!H:H)+H7)/(SUMIF('August 31, 2008'!C:C,Current!C7,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C7,'August 28,
2009'!J:J)+J7)),"",((SUMIF('August 31, 2008'!C:C,Current!C7,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C7,'August 28,
2009'!H:H)+H7)/(SUMIF('August 31, 2008'!C:C,Current!C7,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C7,'August 28, 2009'!J:J)+J7)))

instead it comes with the date: September 10, 2009 like
this:=IF(ISERROR((SUMIF('August 31, 2008'!C:C,September 10, 2009!C7,'August
31, 2008'!H:H)+SUMIF('August 28, 2009'!C:C,September 10, 2009,'August 28,
2009'!H:H)+H7)/(SUMIF('August 31, 2008'!C:C,September 10, 2009!C7,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,September 10, 2009!C7,'August 28,
2009'!J:J)+J7)),"",((SUMIF('August 31, 2008'!C:C,September 10,
2009!C7,'August 31, 2008'!H:H)+SUMIF('August 28, 2009'!C:C,September 10,
2009!C7,'August 28, 2009'!H:H)+H7)/(SUMIF('August 31, 2008'!C:C,September 10,
2009!C7,'August 31, 2008'!J:J)+SUMIF('August 28, 2009'!C:C,September 10,
2009!C7,'August 28, 2009'!J:J)+J7)))

Do you have any suggestions of how to copy the columns to the new sheet,
without the formula changing when I bring it over? So it always just copies
with the sheet name "Current"?

Thank you.
Click to expand...

Why don't you just duplicate the "Current" sheet, rename the copy "September 10,
2009" and then adjust "Current" as needed.
 
G

grateful

I was wondering if there was another way because my new "Current" sheet, is
actually exported from QuickBooks, so there is a lot of new data each week,
and so far it has been easier to just copy and paste columns I to N over
because those are the formulas I have added to the QuickBooks report.

But you are right, that is one way I could do it, that I hadn't considered.

Thank you.
 
G

grateful

Hi, I am very sorry, but I have encountered new problems this week where it
doesn't work, and I'm not sure why.

I can give two examples:

Ex:
Project 1

Sheets H J
Current 18.20 3
August 28, 2009 31,983.61 409.5
August 31, 2008 0 42

The formula is returning 77.58. It should return: 70.41

This is the formula:
=IF(ISERROR((SUMIF('August 31, 2008'!C:C,Current!C54,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C54,'August 28,
2009'!H:H)+H54)/(SUMIF('August 31, 2008'!C:C,Current!C54,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C54,'August 28,
2009'!J:J)+J54)),"",((SUMIF('August 31, 2008'!C:C,Current!C54,'August 31,
2008'!H:H)+SUMIF('August 28, 2009'!C:C,Current!C54,'August 28,
2009'!H:H)+H54)/(SUMIF('August 31, 2008'!C:C,Current!C54,'August 31,
2008'!J:J)+SUMIF('August 28, 2009'!C:C,Current!C54,'August 28,
2009'!J:J)+J54)))

Ex:
Project 2

Sheets H J
Current -1,755.00 0
August 28, 2009 9924.16 98
August 31, 2009 21.5 0

The formula is giving me: 221.71 It should be 83.58

This is the formula I have: =IF(ISERROR((SUMIF('August 31,
2008'!C:C,Current!C57,'August 31, 2008'!H:H)+SUMIF('August 28,
2009'!C:C,Current!C57,'August 28, 2009'!H:H)+H57)/(SUMIF('August 31,
2008'!C:C,Current!C57,'August 31, 2008'!J:J)+SUMIF('August 28,
2009'!C:C,Current!C57,'August 28, 2009'!J:J)+J57)),"",((SUMIF('August 31,
2008'!C:C,Current!C57,'August 31, 2008'!H:H)+SUMIF('August 28,
2009'!C:C,Current!C57,'August 28, 2009'!H:H)+H57)/(SUMIF('August 31,
2008'!C:C,Current!C57,'August 31, 2008'!J:J)+SUMIF('August 28,
2009'!C:C,Current!C57,'August 28, 2009'!J:J)+J57)))


Can you help?

Thank you.
 
T

T. Valko

At this point I think I would need to see the file to be able to figure out
what you're trying to do and find the best way to do it.

If your file is less than 1mb in size and you want me to take a look at it
I'll gladly do so.
 
G

grateful

Hi, Thank you so much for being so helpful. I am going to look at it again
real close, and see if I can see any errors on my part, and Tuesday I will
get back to you and let you know if I can't find it.

Thank you again, for all your help!

Sincerely,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

I found Row 14 in wksh 2, can I get the value of J14 into wksh 1? 13
return date from range of dates with if statement 3
Match Formula for three fields to produce result 5
match function with nested if 12
MATCH problem 3
Sum(indirect(Address...... 3
Interest Calculations 6
Return date if in range, else return blank 1

Top