INDIRECT with conditions problem - spreadsheet copied weekly same rows/colums/names

Hi everyone,

i'm still trying to learn indirect so hoped someone can create a formula for me so i can start with something and add to it as i understand it

Okay so i have a workbook with spreadsheets named Monday of each week like : 01.07 next would be 08.07 etc
Each spreadsheet is a copy of a previous one.

Each has week date i.ex 01/07/2013 in cell D1
I'd like to sum column O of the previous week in column J of current week if columns D and E match D E of previous week


so been trying indirect doing cell D1-7 to get the correct date to sum across all the worksheets but just got a headache..
\1
basically i need to know what were the previous arrears for the children, each week has to have column O contents in column J of the following week if possible..
criteria would be if D1=D1-7
and column D & E which contains name and surname


could do regular sumifs but it doesnt meet my needs...

Anyone willing to help pleaaase?

workbook attached
nursery.xlsx

ps. sorry for the attachments not sure how to do it >.>


can we do something like if D1=D1-7 then =sumifs(O:O,D:D,D4,E:E,E4)
where O = week arrears
D = suname
E = name

i just can't merge it with INDIRECT ;(

Dates have to be formatted. In D1 you have the format MM/DD/YYYY but you indicate you have sheet names formatted MM.DD, not the same thing.

Since INDIRECT() is text function and dates are numeric displayed as text, you have to control the displayed text even inside formulas. At least you do for something like this.

So, to get the correct text string for D1-7, you would need: TEXT(D1-7, "MM.DD") ok?

To put that into your SUMIFS() as an indirect reference:

=SUMIFS(INDIRECT(TEXT(D1-7, "MM.DD")&"!O:O"),INDIRECT(TEXT(D1-7, "MM.DD")&"!O:O"),D4,INDIRECT(TEXT(D1-7, "MM.DD")&"!E:E"),E4)

i think this is what I need although I don't know... maybe I complicate things.
Tried your solution JBeaucaire ( I AM SO GRATEFUL can't remember how many times I tried to automate this.. I always copy week arrears to previous arrears on the new sheet.. which is so time consuming..) but it returns value error I formatted D1 to MM.DD as well as named tabs in same format so MM.DD


I FOUND IT!!!!!!!! the formula had O:D for first criteria instead of O:O
You really made my day and work way easier from this day on! I hope one day I'll be as good as you are! Thank you again!

Glad to help. If you're already using INDIRECT() referencing and understand it, you're well on your way.