formula to return yes if 2 columns meet criteria, and date from master row

**annazet** · 07-08-2013, 07:18 AM

Hi guys,

I'm trying to figure out a way to return yes on weekly created sheets so i don't have to check if a child is funded or not every week.

If you look at "grant" sheet you will see a list of children in columns A and B, then Weeks in row C3 onwards
Then
On each weekly added sheet cell D1 has weeks date formatted DD.WW which corresponds to "GRANT" C3:onwards
On each weekly added sheet in cells D3:D91 i have a list of surnames
in E3:E91 I have a list of names

now what i'd like to achieve is to get a formula on weekly added sheet in column G (to be copied down), that returns either yes or no, if a child has value>0 on "Grant" sheet that matches weeks sheet with week from "GRANT" sheet.

Is there any way? Tried sumifs but, never done something like it and thought someone would do their magic and i could learn new things same time.

workbook attached (don't get scared with ref# as i removed some sheets.
Fees Leigh 13.xlsx

**Ace_XL** · 07-08-2013, 07:26 AM

In G3 of weekly sheet try this arry formula

=IF(INDEX(GRANT!$C$6:$AM$82,MATCH([@[First Name]]&[@Surname],GRANT!$A$6:$A$82&GRANT!$B$6:$B$82,0),MATCH($D$1,GRANT!$C$3:$AM$3,0))>0,"Yes","No")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Copy down

**annazet** · 07-08-2013, 07:32 AM

amazing how fast you replied many thanks! I appreciate lots

hmm returns N/A

have 2007 at work still so changed the formula to
=IF(INDEX(GRANT!$C$6:$AM$82,MATCH([First Name]&[Surname],GRANT!$A$6:$A$82&GRANT!$B$6:$B$82,0),MATCH($D$1,GRANT!$C$3:$AM$3,0))>0,"Yes","No")

**Ace_XL** · 07-08-2013, 07:35 AM

On the 31.12 tab change date to reflect 31.12.2012

also remember to Ctrl+Shift+Enter

**annazet** · 07-08-2013, 07:42 AM

Okay but if I change the date to 31/12/12 which was text before another formula wont work.. (sorry to be such a pain I'm learning as I go and creating my little Frankenstein... bit by bit, it's really challending and interesting)

So all my tabs are named in DD.MM format
and I have a formula in each weekly sheet like:
=SUMIFS(INDIRECT(TEXT($D$1-7, "DD.MM")&"!O:O"),INDIRECT(TEXT($D$1-7, "DD.MM")&"!D:D"),D3,INDIRECT(TEXT($D$1-7, "DD.MM")&"!E:E"),E3)

so i guess D1 has to be in that format as well?
i have a tab called "INCOME" which checks all the sheets and if D1 is A4+ then B4 gets info from that weeks tab cell K1

but to work it has to be in DD.MM ;/