a little complicated

  • Thread starter Thread starter Minitman
  • Start date Start date
M

Minitman

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
 
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
 
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
 
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
 
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.
 
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
 
Pete. Genius is the word.

Thanks a ton.


Pete_UK said:
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
Click to expand...
 
Back
Top