Multiple tab lookup.....

[davin]

http://www.blackiechan.net/help.jpg

I need a formula that will return the "Avg Time Late" for whatever is in "A2" just like a vlookup, however i need it to average the data that it gathers from multiple tabs within the workbook.

[ This Message was edited by: davin on 2002-05-14 15:29 ]

[ This Message was edited by: davin on 2002-05-14 15:30 ]

[ This Message was edited by: davin on 2002-05-14 15:31 ]

[Yogi Anand]

On 2002-05-14 15:29, davin wrote:
http://www.blackiechan.net/help.jpg

I need a formula that will return the "Avg Time Late" for whatever is in "A2" just like a vlookup, however i need it to average the data that it gathers from multiple tabs within the workbook.

[ This Message was edited by: davin on 2002-05-14 15:29 ]

[ This Message was edited by: davin on 2002-05-14 15:30 ]

[ This Message was edited by: davin on 2002-05-14 15:31 ]

Hi davin:
From the jpg that you referenced to, the following data resides in B2:C8

0:08 1:10
0:06 1:00
0:09 1:07
0:01 0:10
0:03 0:25
0:02 0:19
0:57 8:37

The first part of your question is quite clear in that if I put a value of 0:09 in cell A2, I can use the VLOOKUP function as ...

=VLOOKUP(A2,B2:C8,1,0) to pick up 0:09 in column B, and

=VLOOKUP(A2,B2:C8,2,0) to pick up 1:07 from column C

Beyond that, I am not clear about what you are intending to do. A clear explanation of what you are trying to accomplish, perhaps with some sample data would definitely help.

Regards!

[davin]

Yogi,

Assume that all the cells are empty.

If I insert J. DOE into A2, I need it to vlookup J. DOE's time late on 31 worksheets (days of the month), then give me the average. I would put this formula in B2.

I hope this helps

Thanks!
Davin

[ This Message was edited by: davin on 2002-05-14 16:00 ]

[Aladin Akyurek]

On 2002-05-14 15:53, Yogi Anand wrote:

Hi davin:
From the jpg that you referenced to, the following data resides in B2:C8

0:08 1:10
0:06 1:00
0:09 1:07
0:01 0:10
0:03 0:25
0:02 0:19
0:57 8:37

The first part of your question is quite clear in that if I put a value of 0:09 in cell A2, I can use the VLOOKUP function as ...

=VLOOKUP(A2,B2:C8,1,0) to pick up 0:09 in column B, and

=VLOOKUP(A2,B2:C8,2,0) to pick up 1:07 from column C

Beyond that, I am not clear about what you are intending to do. A clear explanation of what you are trying to accomplish, perhaps with some sample data would definitely help.

Regards!

What that jpeg shows are the expected results, not the data that must be averaged and totaled for a key value in column A, which is also left empty.

[davin]

correct, the data to be averaged will be on the 31 worksheets.

[Aladin Akyurek]

On 2002-05-14 16:05, davin wrote:
correct, the data to be averaged will be on the 31 worksheets.

You'll need to use 31 columns after column C in order to lookup in each sheet.

In D2 you'll have:

=VLOOKUP(A2,Sheet1!Range1,2,0)

so on up to AH2.

In C2 you'll have:

=SUMIF(D2:AH2,"<>#N/A")

In B2 you'll have:

=C2/MAX(1,COUNTIF(D2:AH2,"<>#N/A")-COUNTIF(D2:AH2,0))

[davin]

I thought about that, but I was wondering if there was a shorter way.

Thanks!

DB

[Aladin Akyurek]

On 2002-05-14 16:28, davin wrote:
I thought about that, but I was wondering if there was a shorter way.

Thanks!

DB

I don't believe so, Davin.

Don't bother about eliminating #N/A's. The formulas for C2 and D2 do simply allow for #N/A's.

You can hide those 31 columns of course.

Aladin


[ This Message was edited by: Aladin Akyurek on 2002-05-14 16:42 ]

[Yogi Anand]

On 2002-05-14 15:59, davin wrote:
Yogi,

Assume that all the cells are empty.

If I insert J. DOE into A2, I need it to vlookup J. DOE's time late on 31 worksheets (days of the month), then give me the average. I would put this formula in B2.

I hope this helps

Thanks!
Davin

[ This Message was edited by: davin on 2002-05-14 16:00 ]

Now I get it. And the reason you are using VLOOKUP function is because John Doe's name may not appear on all 31 worksheets, and also not in the same row on every worksheet --- we don't want John Doe to be late every day.
However, if you did have John Doe's name in the same cell on the related worksheets when John Doe is late, then it will be a simple matter of picking up the sum of the same cell entries across 31 worksheets.

Regards!