hello everyone,

I hope you're having a good day
I need your help in solving my puzzle

I have my residents fees file where each resident has his/her own spreadsheet.
Each residents spreadsheet consists of 2 grouped sections Top Council accounts Bottom Family accounts
Each section in column A has either "council" or "client" across all rows
A B C D E F G H I J
funded date fee from to days due invoiced paid date
council 27/06/2013 785.60 01/06/2013 28/06/2013 28 3,142.40 27/06/2013
council
then bottom section as above but for family accounts

Each resident spreadsheet is named exactly same way Surname Name, Title and has client name in cell B2 each, invoiced dates in each column H, Paid in column I and then date paid in column J, oh and column A has "client" or "council" in each row

I have a spreadsheet "INVOICES" where I have columns
A B C D E
Date Name Invoiced Details Paid
27/06/2013 Smith John, Mr "link to word file" no
I will enter date, clients name want to find invoiced amount for clients name and amount invoiced
I want to have a formula in column C of "invoices" spreadsheet which will lookup invoiced amount across all the spreadsheets, that has criteria:
- looks through all spreadsheets in cell B2 to match column B of my "invoices" name i.ex i enter john smith, mr and looks for that across sheets in B2
- looks through column A of each spreadsheet looking only for "client" value
- looks through column H of each spreadsheet to match date with the date I enter can be H:H
- looks through column G of each spreadsheet to find amount invoiced on a given date that i'll enter in column A of "invoices"

in short, i want to find invoiced amount for a given person on a given date.

made it so complicated you'll find it silly maybe, but is it something someone could help me out with or advice an easier way please?
MANy THANKS