So far this works only in the same format and applications name.
It doesn't work if appl name(A) and/or format are different in sheet2 and if
new application is added to sheet1. I believe, that refernce to Application
Name should be added into the function. Any solution.
Thanks
"Max" wrote:
Assuming source data as posted is in Sheet1, cols A to C
In Sheet2,
you have this repeating pattern set-up running in A2:B2 down:
db1 Instances/Subsystems
Tables
Indexes
db64 Instances/Subsystems
Tables
Indexes
etc
Put in C2, normal ENTER:
=INDEX(Sheet1!C$2:C$100,MATCH(1,INDEX((Sheet1!A$2: A$100=INDIRECT("A"&(INT((ROWS($1:1)-1)/3)+1)*3-1))*(Sheet1!B$2:B$100=B2),),0))
Copy C2 down as far as required to return the required results. Adapt the
ranges to suit your actuals in Sheet1.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"shulmany" wrote:
Hi All, Please help the due date is today (01/15) by the end of the day
I have series of data in excel spreadsheet (please see the below example)
and have to complete the following:
For each Application (A1)-Lookup by "Application Name" not by row#, I need
to match Object (B1) and its Rating (C1) to update another worksheet.
Application Object Rating
db1 Instances/Subsystems 14
db1 Tables 4305
db1 Indexes 5940
db1 Procedures 1380
db1 Triggers 488
db1 Constraints 573
db1 # of rows 35
db64 Instances/Subsystems 1
db64 Tables 5383
db64 Indexes 4728
db64 Procedures 119
db64 Triggers 31
db64 Constraints 2173
db64 # of rows 150
db51 Instances/Subsystems 2
db51 Tables 230
db51 Indexes 498
db51 Procedures 142
db51 Triggers 0
db51 Constraints 230
db51 # of rows 00
Result:
db1 Instances/Subsystems 14
Tables 4305
Indexes 5940
db64 Instances/Subsystems 1
Tables 5383
Indexes 4728
db51 Instances/Subsystems 2
Tables 230
Indexes 498
Thanks all in advance for fast reponses