lookup help!
 

I have the following similar data in multiple sheets, say sheets 2 through
33, each sheet for each day:

Sheet2:
A B C D E
James Helen Bob Kathy Cindy
Server1 0.25 0.14 0.22 0.42 0.33
Server2 0.22 0.11 0.38 0.29 0.38
Server3 0.48 0.22 0.33 0.28 0.94

lookup help!
 

Well, you can do this with lots of gyrations, but it is tedious and
error-prone. You are far better off logging this data in a single sheet laid
out in some manner such as

Col 1 Date
Col 2 Server
Col 3 User
Col 4 Data value

With data stored in this layout you can EASILY analyze it, filter and sort
it, create pivot tables & pivot charts, etc.



"my" wrote:

I have the following similar data in multiple sheets, say sheets 2 through
33, each sheet for each day:

Sheet2:
A B C D E
James Helen Bob Kathy Cindy
Server1 0.25 0.14 0.22 0.42 0.33
Server2 0.22 0.11 0.38 0.29 0.38
Server3 0.48 0.22 0.33 0.28 0.94
.
.
.
Server282 0.22 0.22 0.32 0.38 0.93

Sheet3:
A B C D E
James Helen Bob Kathy Cindy
Server1 0.25 0.14 0.22 0.42 0.33
Server2 0.22 0.11 0.38 0.29 0.38
Server3 0.48 0.22 0.33 0.28 0.94




In Sheet 1 I would like to summarize all of James's data.

Look up James in Sheets 2 through 33, and retrieve the corresponding number
for each Server.

My result should look like the following in sheet 1:

Server1 0.25 0.25
Server2 0.22 0.22
Server3 0.48 0.48

any suggestions? thanks very much! :D

lookup help!
 

I was going to manually consolidate the data, but then I realized on some
worksheets I would have Server1 through Server282, on other worksheets I am
missing some of the Servers. If I were to do this manually it would take
very long :( Any suggestions using vlookup with two conditions? I would
just like to match James, and which server.

Much appreciate it.

"Duke Carey" wrote:

Well, you can do this with lots of gyrations, but it is tedious and
error-prone. You are far better off logging this data in a single sheet laid
out in some manner such as

Col 1 Date
Col 2 Server
Col 3 User
Col 4 Data value

With data stored in this layout you can EASILY analyze it, filter and sort
it, create pivot tables & pivot charts, etc.



"my" wrote:

I have the following similar data in multiple sheets, say sheets 2 through
33, each sheet for each day:

Sheet2:
A B C D E
James Helen Bob Kathy Cindy
Server1 0.25 0.14 0.22 0.42 0.33
Server2 0.22 0.11 0.38 0.29 0.38
Server3 0.48 0.22 0.33 0.28 0.94
.
.
.
Server282 0.22 0.22 0.32 0.38 0.93

Sheet3:
A B C D E
James Helen Bob Kathy Cindy
Server1 0.25 0.14 0.22 0.42 0.33
Server2 0.22 0.11 0.38 0.29 0.38
Server3 0.48 0.22 0.33 0.28 0.94




In Sheet 1 I would like to summarize all of James's data.

Look up James in Sheets 2 through 33, and retrieve the corresponding number
for each Server.

My result should look like the following in sheet 1:

Server1 0.25 0.25
Server2 0.22 0.22
Server3 0.48 0.48

any suggestions? thanks very much! :D

lookup help!
 

you might try the {=sum(if(conditiona=x), if(conditionb=y),calc)} array
formula by using ctrl+shft+enter after typing it. You'll get curly braces if
you do it correctly.

Robert

"my" wrote:

I was going to manually consolidate the data, but then I realized on some
worksheets I would have Server1 through Server282, on other worksheets I am
missing some of the Servers. If I were to do this manually it would take
very long :( Any suggestions using vlookup with two conditions? I would
just like to match James, and which server.

Much appreciate it.

"Duke Carey" wrote:

Well, you can do this with lots of gyrations, but it is tedious and
error-prone. You are far better off logging this data in a single sheet laid
out in some manner such as

Col 1 Date
Col 2 Server
Col 3 User
Col 4 Data value

With data stored in this layout you can EASILY analyze it, filter and sort
it, create pivot tables & pivot charts, etc.



"my" wrote:

I have the following similar data in multiple sheets, say sheets 2 through
33, each sheet for each day:

Sheet2:
A B C D E
James Helen Bob Kathy Cindy
Server1 0.25 0.14 0.22 0.42 0.33
Server2 0.22 0.11 0.38 0.29 0.38
Server3 0.48 0.22 0.33 0.28 0.94
.
.
.
Server282 0.22 0.22 0.32 0.38 0.93

Sheet3:
A B C D E
James Helen Bob Kathy Cindy
Server1 0.25 0.14 0.22 0.42 0.33
Server2 0.22 0.11 0.38 0.29 0.38
Server3 0.48 0.22 0.33 0.28 0.94




In Sheet 1 I would like to summarize all of James's data.

Look up James in Sheets 2 through 33, and retrieve the corresponding number
for each Server.

My result should look like the following in sheet 1:

Server1 0.25 0.25
Server2 0.22 0.22
Server3 0.48 0.48

any suggestions? thanks very much! :D

lookup help!
 

Well - how are your sheets named. Sheet1, Sheet2, etc? Are all the sheets
laid out identically, with James in the same column? If you answer yes to
both of those, then:

1) Insert a new worksheet into the workbook
2) in A2 enter the formula = "Sheet"&row(a2)&"!A2:B500" (assumes James is
always in col B)
3) in A1 enter the server name/number, i.e. "Server282"
4) 5) in B2 use the formula

=vlookup($A$1,indirect(A2),2,false)

Copy the formulas in A2 & B2 down far enough to handle all the sheets in
your workbook






"my" wrote:

I was going to manually consolidate the data, but then I realized on some
worksheets I would have Server1 through Server282, on other worksheets I am
missing some of the Servers. If I were to do this manually it would take
very long :( Any suggestions using vlookup with two conditions? I would
just like to match James, and which server.

Much appreciate it.

"Duke Carey" wrote:

Well, you can do this with lots of gyrations, but it is tedious and
error-prone. You are far better off logging this data in a single sheet laid
out in some manner such as

Col 1 Date
Col 2 Server
Col 3 User
Col 4 Data value

With data stored in this layout you can EASILY analyze it, filter and sort
it, create pivot tables & pivot charts, etc.



"my" wrote:

I have the following similar data in multiple sheets, say sheets 2 through
33, each sheet for each day:

Sheet2:
A B C D E
James Helen Bob Kathy Cindy
Server1 0.25 0.14 0.22 0.42 0.33
Server2 0.22 0.11 0.38 0.29 0.38
Server3 0.48 0.22 0.33 0.28 0.94
.
.
.
Server282 0.22 0.22 0.32 0.38 0.93

Sheet3:
A B C D E
James Helen Bob Kathy Cindy
Server1 0.25 0.14 0.22 0.42 0.33
Server2 0.22 0.11 0.38 0.29 0.38
Server3 0.48 0.22 0.33 0.28 0.94




In Sheet 1 I would like to summarize all of James's data.

Look up James in Sheets 2 through 33, and retrieve the corresponding number
for each Server.

My result should look like the following in sheet 1:

Server1 0.25 0.25
Server2 0.22 0.22
Server3 0.48 0.48

any suggestions? thanks very much! :D

lookup help!
 

To produce a single consolidated view of the 33 sheets and a pivot table
for the report, you could try the technique described at:
http://www.excelforum.com/showthread...ighlight=pivot
regards..


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=527274