lookup help!

[Guest]

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[steven1001]

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.php?t=527461&highlight=pivot
regards..