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
then bottom section as above but for family accounts
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
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
I will enter date, clients name want to find invoiced amount for clients name and amount invoiced
A B C D E Date Name Invoiced Details Paid 27/06/2013 Smith John, Mr "link to word file" no
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
Bookmarks