Finding a person's specific Data with a formula

**Kram222** · 09-10-2012, 09:05 PM

I have a report sheet that is attached to multiple sheets.

I am manually taking the data out for each week for each person.

Is there a formula for it to automatically find the person's name and check what data is needed for that cell then go to the right sheet, find that person and find the specific data?

**NickyC** · 09-10-2012, 09:16 PM

Hi Kramm222
I expect a formula or macro could be written, but we'd need more information, eg:

where should the formula look for the person's name (sheet name and range),
where is the data to be copied (e.g. column to the right of the name)
where is the data to be copied to

**Kram222** · 09-10-2012, 09:39 PM

Originally Posted by NickyC

Hi Kramm222
I expect a formula or macro could be written, but we'd need more information, eg:

where should the formula look for the person's name (sheet name and range),
where is the data to be copied (e.g. column to the right of the name)
where is the data to be copied to

Here is the Document that I'm talking about.
If you check under the "utilities Sheet" I wrote down as clearly as I can express what I need the formula to do.

I know I might need to change some of the titles in my tables. but other than that what can i do?

**NickyC** · 09-11-2012, 02:20 AM

Hi
this macro might work
to explain:

it finds the last block of characters in the title cell (ie cell A53), which is the week number
for each cell in the range "teachers" (the range in shee Utility with the names in), it:

finds the column with that name in row 2 of the sheet with the name name as the week number
finds the value row in 361 of the column to the right of that column
copies this value into the cell 3 columns to the right of the teacher's name in sheet "utility"

Please Login or Register  to view this content.

**Kram222** · 09-12-2012, 09:19 PM

Thanks!

This works perfectly!

The only 3 issues are
1) I'm not familiar enough with macro's yet to be able to understand the coding
2)because of reason 1 I dont know how to adjust this to make it work for the other columns
3) I was hoping more on finding out how to use a formula/function instead of a macro

For anyone reading this:
This Macro DOES WORK!!!
It's just not what i was looking for.

**Haseeb Avarakkan** · 09-12-2012, 10:21 PM

Try this in F55

=VLOOKUP("Total Hrs",INDIRECT("'"&RIGHT(A$53,4)&"'!B:ALL"),MATCH(C55,INDIRECT("'"&RIGHT(A$53,4)&"'!B2:ALL2"),0)+1,0)

then copy down.

**Kram222** · 09-12-2012, 10:37 PM

Originally Posted by Haseeb A

Try this in F55

=VLOOKUP("Total Hrs",INDIRECT("'"&RIGHT(A$53,4)&"'!B:ALL"),MATCH(C55,INDIRECT("'"&RIGHT(A$53,4)&"'!B2:ALL2"),0)+1,0)

then copy down.

This is what i was looking for.
only issue is that I dont get it.
could anyone explain the layout of this formula?