How to use if function to trafer data to 2nd sheet based on date?

**jgomez** · 03-31-2011, 05:50 PM

First of all thanks to all of those who helped me out the last week! Most of those formulas are in this sheet.

On EOM tracker I have this formula:

=IF(Commercial!W2>='EOM Tracker'!$E$3,"",Commercial!E2)

(I actually wrote it!) Might explain why its not working completely.
I would like for it to transfer over data into the EOM tracker if the date past due on the commercial sheet is less than or equal to 2/28/11. The only thing is not doing is transferring over info. if the date is 2/28/11… its transferring anything that is less.

Any ideas why?

Oh this is a side question. I’m not very good at writing formulas that have multiple functions but I noticed that on some like this one:

SUMPRODUCT(--(I6:I5000="no"),--(ISNUMBER(SEARCH("Lake",H6:H5000))),D6:D5000)

There is the – symbol… why is that? I know it’s a newbie question but I’d like to know. I get the formula, just don’t get that part.

**madadd** · 03-31-2011, 06:05 PM

In order to put conditional information in a second sheet, that's where the formula has to be (in the second sheet). You can easily refer to the first sheet and display that information or do the "" to display nothing. If your equation that you posted is wrong, check for grammar. I already see a ' missing from the end of 'EOM Tracker'!$E$3,

As for the "--" basically it takes a conditional statement and turn it into a 1 (true) or 0 (false) so that in a sumproduct, values are only summed if the the conditions are true. 1*1*D6:D5000. So likewise if one is false, its a multiplcation by 0 which yields a 0 value and thus that particular value in the series D6:D5000 isn't summed.