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

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

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

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

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

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..