to insert a master sheet with formulas which can verify other work sheets in it

dear friends

i have attached a file its a salary sheet (sample file) for three months, in this file i want to insert a master sheet where all the advance deductions are verified that they are properly deducted (highlighted the columns) . the person name will be verified with fathers name and they will keep on changing. First month will be compared with the next month, so please help me to create it as there are two type of advances old and new. I hope every thing is clear enough or let me know?.

Thanks in Advance

Manoj Sharma

thanks FDibbins for your reply and so sorry for late reply.

well my need is to verify that all the deduction of advance amount is done properly and no advance is given to ghost worker. i will get every month a sheet of previous months salary in excel file and than i will copy paste it to my file and than either it should show me in my master sheet where all the persons name should be automatically updated by every sheet if it contain new person.I hope it is clear to you now otherwise i do one thing that i try to attach a sample file with a non working master sheet for your reference.

dear friend i have attached the file please help me and you are open to modify the way it can be managed easy.

Thanks for the early reply
i will try this with my original data and then let you know, but how will it take the names itself or i have to do it manually as every month the persons place is changed and addition/deletions is always done. please advice me and i will do it the same and once again thanks for your early reply my dear friend FDbbins.

Originally Posted by FDibbins

Take a look at the attached. I have changed your headings on Master, to match your sheet names (for the 1st 2 months anyway)

All you need to do now, is copy the formula "set" from F-H over to each month, and it should pull in the data for you

Dear Friend FDibbins,

Hello, i just go through your formulas and find that first of all the person is not verified by his fathers name and how will it pick the data from new sheets as i am new and only learned excel from expert people like you. so please explain it in detail , thanks and wish you a very happy week'd. Enjoy.

Thanks
Manoj Sharma

Originally Posted by greatairi

Thanks for the early reply
i will try this with my original data and then let you know, but how will it take the names itself or i have to do it manually as every month the persons place is changed and addition/deletions is always done. please advice me and i will do it the same and once again thanks for your early reply my dear friend FDbbins.

Well Friend i have put the formulas as per your instructions while it took many attempts as i am not trained in excel and they are working perfectly.
But i have one more request if it is possible that the column A and B (person name-column A and Fathers name column B) get updated as a new sheet is attached i.e New persons are added. waiting for your reply , THANKS.

Manoj Sharma

Originally Posted by greatairi

Thanks for the early reply
i will try this with my original data and then let you know, but how will it take the names itself or i have to do it manually as every month the persons place is changed and addition/deletions is always done. please advice me and i will do it the same and once again thanks for your early reply my dear friend FDbbins.

No replies Yet...... waiting for a solution so that I can mark it solved..................

is there no solution...........

Please help me on this as i requested in my post that person name should be verified by his fathers name and than it should pick the data but here FDibbins has solved almost every thing but the formula is only working with person name and not verifying with his fathers name so it is not working perfectly as i requested. Please help me out??????

Thanks for your reply, i hope that it is possible with excel formulas. But it is very strange that why others are not attempting.I have also requested some of my great guru's like you to solve.Lets hope the best will request you to keep on trying..........

Originally Posted by FDibbins

That part has me stumped - for now. But I have asked for help from the other experts and guru's

thank you - Sir Genius,

it is working perfect on my sample sheet but for my original sheet I will have to work hard as the cell number is changed. But I will try my best otherwise I know you are always there. I have become a big fan of yours and really appreciate your Expertise. There is one request that if a formula in G 3 can be changed as I have to take figures from the work sheets and then verify that every thing is perfect, so please help me . Thank you in advance....

Manoj Sharma

Originally Posted by JBeaucaire

1) Starting formula:

E3: =INDEX(INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!K1:K1000"),MATCH($A3&$B3,INDEX(INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!A1:A1000")&INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!B1:B1000"),0),0))

F3: =SUM(OFFSET(INDEX(INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!K1:K1000"),MATCH($A3&$B3,INDEX(INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!A1:A1000")&INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!B1:B1000"),0),0)),,1,,4))

G3: =SUM(E3:F3)

H3: =INDEX(INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!Q1:Q1000"),MATCH($A3&$B3,INDEX(INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!A1:A1000")&INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!B1:B1000"),0),0))


2) Now copy E3:B3 down through row 17 and the first set of data appears. You will see #N/A for any name combinations not found in that month's data sheet.

3) Now copy E3:B17 and paste in I3.

4) Now copy E3:B17 and paste in M3.

5) Anytime you add a new month's data sheet, copy the formulas over into that section on the master to collect the data.

http://screencast.com/t/LZ8pyuymB02

Thank You Sir.........

Originally Posted by JBeaucaire

G3 would be:
=INDEX(INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!P1:P1000"),MATCH($A3&$B3,INDEX(INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!A1:A1000")&INDIRECT(TEXT(E$1,"'MMMM-YYY'")&"!B1:B1000"),0),0))


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.