[SOLVED]If with Name Range

[jackson_hollon]

I have set up 3 name ranges.
1) D9:D14 Date
2) E9:E14 Commission
3) F9:F14 Paid

I want row 2 automatically recognized the correspond Commission amount when the week has included the first day of the month.

I also want row 4 to accumulate the Commission if prior paid name range show N.

I believe I need to use if statement. Any formula will be fine as long as it works with name ranges.

Thanks.

Jackson

[FDibbins]

Any formula will be fine as long as it works with name ranges.

Not sure why you need that, the formula below works just fine for what you want...
=VLOOKUP(IF(DAY(G$1)>1,EOMONTH(G$1,0)+1,G$1),$D$10:$E$14,2,1)

However, if you MUST use ranges, then your range needs to include both columns for the vlookup...
=VLOOKUP(IF(DAY(G$1)>1,EOMONTH(G$1,0)+1,G$1),range,2,1)
where range is the range name for D10:E14

This will give you the sum...
=IF(C2="","",SUM($C$4:C4))

[jackson_hollon]

Ford,

Thanks for the help.

Both formulas have a little bit problems. They do not work the way I want. Both formulas gave me value in every week.

The following are what I want:

For row 2, I only want the value in the week that include the first day of the month.

For row 4, I only want the value accumulated if paid column in the prior month is N.

Could you please give some more help?

Thanks again for the help.

Jackson

[FlameRetired]

Edited, again:

I'm not sure that I get it. Have a look at the attached.

[jackson_hollon]

FlameRetired,

Thanks for the help.

It is almost there. Except the date is one week behind on your formula. For example: Jan 1st should be included in week of column K. On your formula, it showed it is on column L.

Thanks again for the help.

[FDibbins]

I have come up with this, but most of the dates that pull in data, do not match with yours.

=IF(AND(EDATE(EOMONTH(C$1,0)+1,-1)-7<C$1,EDATE(EOMONTH(C$1,0)+1,-1)+7>C$1),VLOOKUP(IF(DAY(C$1)>1,EOMONTH(C$1,0)+1,C$1),$D$13:$E$17,2,1),"")

edit: FT is having the same problem as I am - not taking into account when the end of the month is in the week with the 1st day of the next month

[FlameRetired]

I have come up with this, but most of the dates that pull in data, do not match with yours.

=IF(AND(EDATE(EOMONTH(C$1,0)+1,-1)-7<C$1,EDATE(EOMONTH(C$1,0)+1,-1)+7>C$1),VLOOKUP(IF(DAY(C$1)>1,EOMONTH(C$1,0)+1,C$1),$D$13:$E$17,2,1),"")

edit: FT is having the same problem as I am - not taking into account when the end of the month is in the week with the 1st day of the next month

Thank you, Ford. I thought I was the only one...

I also suspect I've been looking at the math backwards......I'm attempting another go at this one.

Flame

[jackson_hollon]

Thank you Ford and Flame,

I am most appreciated for all of your effort. I had played around for hours and didn't even be able to get close to what you got.

Thank you again.

Jackson

[FlameRetired]

I think the attached will resolve the remaining questions. Please let us know.

[jackson_hollon]

Flame,

It works perfectly.

Thanks for the help.

Jackson

[FlameRetired]

You're welcome. Glad it worked.

Edit: and thanks for the rep.