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?

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
wrote in message
...
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?

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. Its 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


With regards
Mr. Sarfaraj



"Bob Phillips" wrote:

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
wrote in message
...
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?

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. Its 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


With regards
Mr. Sarfaraj

"Bob Phillips" wrote:

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
wrote in message
...
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?

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(INDIR ECT("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"
soft.com wrote in message
...
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


With regards
Mr. Sarfaraj

"Bob Phillips" wrote:

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
wrote in message
...
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?

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)

"Bob Phillips" wrote in message
...
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(INDIR ECT("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"
soft.com wrote in
message ...
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


With regards
Mr. Sarfaraj

"Bob Phillips" wrote:

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
wrote in message
...
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?

Thank you very much I just want to know more about you can you send me any
mail to my personal e-mail ID

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" wrote:

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)

"Bob Phillips" wrote in message
...
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(INDIR ECT("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"
soft.com wrote in
message ...
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


With regards
Mr. Sarfaraj

"Bob Phillips" wrote:

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
wrote in message
...
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?

=SUMPRODUCT(SUMIF(INDIRECT(ROW(INDIRECT("1:31"))&" !D8:D68");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)
!D8:D68 (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