a little complicated
 

Hey Pete,

Did you mean:

=IF(INDIRECT($A$3&"!A"&ROW(A10))="","",INDIRECT($A $3&"!A"&ROW(A10)))

I noticed a couple of missing )'s And wasn't sure if the OP would have
caught it. (G)

-Minitman



On Mon, 17 Mar 2008 19:01:33 -0000, "Pete_UK"
wrote:

Put this formula in A7 of the summary sheet, then:

=IF(INDIRECT($A$3&"!A"&ROW(A10)="","",INDIRECT($A $3&"!A"&ROW(A10))

and copy this down to get the names from the sheet selected in cell A3. You
could then have your lookup formulae in B7:

=IF($A7="","",VLOOKUP($A7,INDIRECT($A$3&"!A10:F25 6"),COLUMN(B1),0))

You can copy this across for as many columns as you want to return, and then
copy these formulae down as required.

Hope this helps.

Pete

"Gaurav" wrote in message
...
Thanks Pete.

The 3 sheets for 3 processes have exactly the same format. The names are
in Column A, from A10 to A256.
In the summary sheet, I have the dropdown in A3 which has the same 3 names
as the 3 sheet tabs. For example A, B and C.
If I select A in the dropdown, I want the names from SheetA to be returned
in A7 downwards in the same sheet (the summary sheet). Same way I need to
return data from other columns as well....like employee codes in column B,
hours in column C etc.

In simple words, I need the formula to look at the Name in A3, match it
with the sheet name and return the data from that particular sheet.

I hope I was able to explain myself better this time.

Thanks again for looking at it.




"Pete_UK" wrote in message
...
It would help if you tell us a bit about how your data is laid out. For
example, what cell is your drop-down in? Where are your names in sheets
A, B and C? Do they all start in the same cell, e.g. A2? Approximately
how many names do we have in each? If you don't give details like this
then you will end up with a generic solution where certain assumptions
are made and you might find it difficult to apply to your exact
situation.

Pete

"Gaurav" wrote in message
...
FYI, I have done the latter. But I still need help on the former.

Please let me know if I need to provide more information or if I need to
expain it in a better way. I really need help with this.

Thanks

"Gaurav" wrote in message
...
Hi All,

Here is what I am trying to do.

I have 3 sheets (A, B and C) for 3 different processes to track the
overtime of the employees.
I have a summary sheet on which I have a dropdown that lists all 3
processes. Now I want the employees' names to be returned when I select
the process name. For example, I select A in the dropdown, below that I
want all the names that are there in Sheet A. Rest of the information
can be pulled by vlookup once I have the key info.

Another thing. Once the formula returns the names, I would also want
(in a different column) the total OT hours of that particular employee.
The name can appear in all the 3 sheets. I hope it makes sense.

Thanks
Gaurav

a little complicated
 

Hi All,

Here is what I am trying to do.

I have 3 sheets (A, B and C) for 3 different processes to track the overtime
of the employees.
I have a summary sheet on which I have a dropdown that lists all 3
processes. Now I want the employees' names to be returned when I select the
process name. For example, I select A in the dropdown, below that I want all
the names that are there in Sheet A. Rest of the information can be pulled
by vlookup once I have the key info.

Another thing. Once the formula returns the names, I would also want (in a
different column) the total OT hours of that particular employee. The name
can appear in all the 3 sheets. I hope it makes sense.

Thanks
Gaurav

a little complicated
 

FYI, I have done the latter. But I still need help on the former.

Please let me know if I need to provide more information or if I need to
expain it in a better way. I really need help with this.

Thanks

"Gaurav" wrote in message
...
Hi All,

Here is what I am trying to do.

I have 3 sheets (A, B and C) for 3 different processes to track the
overtime of the employees.
I have a summary sheet on which I have a dropdown that lists all 3
processes. Now I want the employees' names to be returned when I select
the process name. For example, I select A in the dropdown, below that I
want all the names that are there in Sheet A. Rest of the information can
be pulled by vlookup once I have the key info.

Another thing. Once the formula returns the names, I would also want (in a
different column) the total OT hours of that particular employee. The name
can appear in all the 3 sheets. I hope it makes sense.

Thanks
Gaurav

a little complicated
 

It would help if you tell us a bit about how your data is laid out. For
example, what cell is your drop-down in? Where are your names in sheets A, B
and C? Do they all start in the same cell, e.g. A2? Approximately how many
names do we have in each? If you don't give details like this then you will
end up with a generic solution where certain assumptions are made and you
might find it difficult to apply to your exact situation.

Pete

"Gaurav" wrote in message
...
FYI, I have done the latter. But I still need help on the former.

Please let me know if I need to provide more information or if I need to
expain it in a better way. I really need help with this.

Thanks

"Gaurav" wrote in message
...
Hi All,

Here is what I am trying to do.

I have 3 sheets (A, B and C) for 3 different processes to track the
overtime of the employees.
I have a summary sheet on which I have a dropdown that lists all 3
processes. Now I want the employees' names to be returned when I select
the process name. For example, I select A in the dropdown, below that I
want all the names that are there in Sheet A. Rest of the information can
be pulled by vlookup once I have the key info.

Another thing. Once the formula returns the names, I would also want (in
a different column) the total OT hours of that particular employee. The
name can appear in all the 3 sheets. I hope it makes sense.

Thanks
Gaurav

a little complicated
 

Thanks Pete.

The 3 sheets for 3 processes have exactly the same format. The names are in
Column A, from A10 to A256.
In the summary sheet, I have the dropdown in A3 which has the same 3 names
as the 3 sheet tabs. For example A, B and C.
If I select A in the dropdown, I want the names from SheetA to be returned
in A7 downwards in the same sheet (the summary sheet). Same way I need to
return data from other columns as well....like employee codes in column B,
hours in column C etc.

In simple words, I need the formula to look at the Name in A3, match it with
the sheet name and return the data from that particular sheet.

I hope I was able to explain myself better this time.

Thanks again for looking at it.




"Pete_UK" wrote in message
...
It would help if you tell us a bit about how your data is laid out. For
example, what cell is your drop-down in? Where are your names in sheets A,
B and C? Do they all start in the same cell, e.g. A2? Approximately how
many names do we have in each? If you don't give details like this then
you will end up with a generic solution where certain assumptions are made
and you might find it difficult to apply to your exact situation.

Pete

"Gaurav" wrote in message
...
FYI, I have done the latter. But I still need help on the former.

Please let me know if I need to provide more information or if I need to
expain it in a better way. I really need help with this.

Thanks

"Gaurav" wrote in message
...
Hi All,

Here is what I am trying to do.

I have 3 sheets (A, B and C) for 3 different processes to track the
overtime of the employees.
I have a summary sheet on which I have a dropdown that lists all 3
processes. Now I want the employees' names to be returned when I select
the process name. For example, I select A in the dropdown, below that I
want all the names that are there in Sheet A. Rest of the information
can be pulled by vlookup once I have the key info.

Another thing. Once the formula returns the names, I would also want (in
a different column) the total OT hours of that particular employee. The
name can appear in all the 3 sheets. I hope it makes sense.

Thanks
Gaurav

a little complicated
 

Put this formula in A7 of the summary sheet, then:

=IF(INDIRECT($A$3&"!A"&ROW(A10)="","",INDIRECT($A$ 3&"!A"&ROW(A10))

and copy this down to get the names from the sheet selected in cell A3. You
could then have your lookup formulae in B7:

=IF($A7="","",VLOOKUP($A7,INDIRECT($A$3&"!A10:F256 "),COLUMN(B1),0))

You can copy this across for as many columns as you want to return, and then
copy these formulae down as required.

Hope this helps.

Pete

"Gaurav" wrote in message
...
Thanks Pete.

The 3 sheets for 3 processes have exactly the same format. The names are
in Column A, from A10 to A256.
In the summary sheet, I have the dropdown in A3 which has the same 3 names
as the 3 sheet tabs. For example A, B and C.
If I select A in the dropdown, I want the names from SheetA to be returned
in A7 downwards in the same sheet (the summary sheet). Same way I need to
return data from other columns as well....like employee codes in column B,
hours in column C etc.

In simple words, I need the formula to look at the Name in A3, match it
with the sheet name and return the data from that particular sheet.

I hope I was able to explain myself better this time.

Thanks again for looking at it.




"Pete_UK" wrote in message
...
It would help if you tell us a bit about how your data is laid out. For
example, what cell is your drop-down in? Where are your names in sheets
A, B and C? Do they all start in the same cell, e.g. A2? Approximately
how many names do we have in each? If you don't give details like this
then you will end up with a generic solution where certain assumptions
are made and you might find it difficult to apply to your exact
situation.

Pete

"Gaurav" wrote in message
...
FYI, I have done the latter. But I still need help on the former.

Please let me know if I need to provide more information or if I need to
expain it in a better way. I really need help with this.

Thanks

"Gaurav" wrote in message
...
Hi All,

Here is what I am trying to do.

I have 3 sheets (A, B and C) for 3 different processes to track the
overtime of the employees.
I have a summary sheet on which I have a dropdown that lists all 3
processes. Now I want the employees' names to be returned when I select
the process name. For example, I select A in the dropdown, below that I
want all the names that are there in Sheet A. Rest of the information
can be pulled by vlookup once I have the key info.

Another thing. Once the formula returns the names, I would also want
(in a different column) the total OT hours of that particular employee.
The name can appear in all the 3 sheets. I hope it makes sense.

Thanks
Gaurav

a little complicated
 

Pete. Genius is the word.

Thanks a ton.


"Pete_UK" wrote in message
...
Put this formula in A7 of the summary sheet, then:

=IF(INDIRECT($A$3&"!A"&ROW(A10)="","",INDIRECT($A$ 3&"!A"&ROW(A10))

and copy this down to get the names from the sheet selected in cell A3.
You could then have your lookup formulae in B7:

=IF($A7="","",VLOOKUP($A7,INDIRECT($A$3&"!A10:F256 "),COLUMN(B1),0))

You can copy this across for as many columns as you want to return, and
then copy these formulae down as required.

Hope this helps.

Pete

"Gaurav" wrote in message
...
Thanks Pete.

The 3 sheets for 3 processes have exactly the same format. The names are
in Column A, from A10 to A256.
In the summary sheet, I have the dropdown in A3 which has the same 3
names as the 3 sheet tabs. For example A, B and C.
If I select A in the dropdown, I want the names from SheetA to be
returned in A7 downwards in the same sheet (the summary sheet). Same way
I need to return data from other columns as well....like employee codes
in column B, hours in column C etc.

In simple words, I need the formula to look at the Name in A3, match it
with the sheet name and return the data from that particular sheet.

I hope I was able to explain myself better this time.

Thanks again for looking at it.




"Pete_UK" wrote in message
...
It would help if you tell us a bit about how your data is laid out. For
example, what cell is your drop-down in? Where are your names in sheets
A, B and C? Do they all start in the same cell, e.g. A2? Approximately
how many names do we have in each? If you don't give details like this
then you will end up with a generic solution where certain assumptions
are made and you might find it difficult to apply to your exact
situation.

Pete

"Gaurav" wrote in message
...
FYI, I have done the latter. But I still need help on the former.

Please let me know if I need to provide more information or if I need
to expain it in a better way. I really need help with this.

Thanks

"Gaurav" wrote in message
...
Hi All,

Here is what I am trying to do.

I have 3 sheets (A, B and C) for 3 different processes to track the
overtime of the employees.
I have a summary sheet on which I have a dropdown that lists all 3
processes. Now I want the employees' names to be returned when I
select the process name. For example, I select A in the dropdown,
below that I want all the names that are there in Sheet A. Rest of the
information can be pulled by vlookup once I have the key info.

Another thing. Once the formula returns the names, I would also want
(in a different column) the total OT hours of that particular
employee. The name can appear in all the 3 sheets. I hope it makes
sense.

Thanks
Gaurav

a little complicated
 

Thanks for the feedback, Gaurav, and to Minitman for his corrections.

Pete

On Mar 17, 8:05*pm, "Gaurav" wrote:
Pete. Genius is the word.

Thanks a ton.