Master Worksheet Display

[Guest]

I have 30 worksheet where all the employee name, salary number and overtime
have

Out put and result …………..> I want to make a master worksheet where I just
enter (in a cell) one employee salary number and it will display all the
employee overtime searching from all 30 worksheet. How? Only through a
formula can any one help me now?

 

[Bob Phillips]

Put the names of all 30 sheets in M1:M30

then use this formula assuming the name is in column A, overtime amount in
column H

=SUMPRODUCT(SUMIF(INDIRECT(M1:M30&"!A2:A20"),"Bob",INDIRECT(M1:M30&"!H2:H20")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Master worksheet searching Formula" <Master worksheet searching
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Guest]

Dear Mr. Bob

I repeat my question again

I have a excel file and within that I have one month daily over time every
day I have one worksheet and work sheet name is 1, 2, 3 like up to 30 every
day one sheet with six title such as (Sl No./EMP PR./EMPLOYEE
NAME/TRADE/HOURS/ REMARKS) now question is what I want to do?.

After completion month and end of workbook I want to add one more worksheet
No.31 and there have one cell where user can enter only the employee salary
Number such as 0036 for example and it will display in same worksheet below
after few column total day of the month such as starting 1 to 30 every cell
contain one day such as 1,2,3,4,…..
Then below after searching it will collect overtime value from the 30
worksheet for that 0036 employee only not any other employee data. It’s just
like a report about one special employee for complete hole month overtime
report when user enter his salary number 0036. suppose every day we have 500
employee overtime sheet and like that we have 30 sheet it means (500 employee
x 30 worksheet) so out of 500 employee and 30 different worksheet your
function will search only one particular employee base on salary number and
display the result while user enter his salary number in new worksheet. As I
hope it is clear for you.

Thanks for your cooperation actually what I need you did not get it. if
possible can you send me your e-mail address then I can send you the excel
file and in last work sheet I will show you how and what I need to be
display?. I hope it more simple to understand. This is my e-mail address
<[email protected]>

With regards
Mr. Sarfaraj

 

[Guest]

Dear Mr. Bob

I repeat my question again

I have a excel file and within that I have one month daily over time every
day I have one worksheet and work sheet name is 1, 2, 3 like up to 30 every
day one sheet with six title such as (Sl No./EMP PR./EMPLOYEE
NAME/TRADE/HOURS/ REMARKS) now question is what I want to do?.

After completion month and end of workbook I want to add one more worksheet
No.31 and there have one cell where user can enter only the employee salary
Number such as 0036 for example and it will display in same worksheet below
after few column total day of the month such as starting 1 to 30 every cell
contain one day such as 1,2,3,4,…..
Then below after searching it will collect overtime value from the 30
worksheet for that 0036 employee only not any other employee data. It’s just
like a report about one special employee for complete hole month overtime
report when user enter his salary number 0036. suppose every day we have 500
employee overtime sheet and like that we have 30 sheet it means (500 employee
x 30 worksheet) so out of 500 employee and 30 different worksheet your
function will search only one particular employee base on salary number and
display the result while user enter his salary number in new worksheet. As I
hope it is clear for you.

Thanks for your cooperation actually what I need you did not get it. if
possible can you send me your e-mail address then I can send you the excel
file and in last work sheet I will show you how and what I need to be
display?. I hope it more simple to understand. This is my e-mail address
<[email protected]>

With regards
Mr. Sarfaraj

 

[Bob Phillips]

And I repeat my response.

Actually if you name the sheets 1 - 30, you can do away with the list in
M1:M30 and use

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1:30"))&"!A2:A20"),"Bob",INDIRECT("Sheet"&ROW(INDIRECT("1:30"))&"!H2:H20")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Master worksheet searching Formula"

 

[Bob Phillips]

Should have been

=SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT("1:30"))&"!A2:A20"),"0036",INDIRECT(ROW(INDIRECT("1:30"))&"!H2:H20")))



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thank you very much I just want to know more about you can you send me any
mail to my personal e-mail ID <[email protected]>

I am sarfaraj working in Saudi Arabia what about you…this is my mobile No.
+966 501401359 can you tell me where are you now?....

I will be waiting
With regards
Mr. Sarfaraj

 

[Bob Phillips]

I would beware of putting my email and phone details on a public forum if I
were you.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Master worksheet searching Formula"

 

[Guest]

=SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT("1:31"))&"!D868");D8;(INDIRECT(ROW(INDIRECT("1:31"))&"!E8:E68"))))

Hi dear BOB

Can you check this I need to display only the employee name not any over time.
1:31 (sheet name)
!D868 (Salary Number in all 31 worksheet)
D8(user enter the employee salary Number)
!E8:E68 (Employee Name in all 31 worksheet)

Now when the user enter in D8 cell salary number it will search all 31
worksheet and display the name of the employer from !E8:E68.

With regards
Mr. Sarfaraj

 

[Bob Phillips]

Will the name be on every sheet?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Master worksheet searching Formula"