"Bill" wrote
That's a interesting approach, Max. Unfortunately, I'm not able to
modify
the data worksheet to include the extra column to concatenate the info in
the
columns of interest. I'm only able to access it and need to display the
summary information in the worksheet with the formulas I'm trying to get
to
work.
Do you have any idea why the formulas I've written do not work? Or how
they
can be modified to produce the desired result?
We could dispense with the concat col D in Sheet1, Bill
by using a "similar looking" array formula in Sheet2
Assuming the same set-ups in Sheets 1 and 2 earlier
(minus col D in Sheet1)
In Sheet2
-------------
Put instead in B2**:
=IF(ISNA(MATCH(TRIM($A2&B$1),TRIM(Sheet1!$A$2:$A$1 00 &
Sheet1!$B$2:$B$100),0)),"",INDEX(Sheet1!$C$2:$C$10 0,MATCH(TRIM($A2&B$1),TRIM
(Sheet1!$A$2:$A$100 & Sheet1!$B$2:$B$100),0)))
Array-enter with CTRL+SHIFT+ENTER
instead of just pressing ENTER
Copy across to C2, fill down
Adapt the ranges to suit: Sheet1!$A$2:$A$100, etc
(but we can't use entire col references now)
**You could also try in B2:
=IF(ISNA(MATCH(TRIM($A2&B$1),TRIM(Name &
Num),0)),"",INDEX(Mon,MATCH(TRIM($A2&B$1),TRIM(Nam e & Num),0)))
(array-entered, and filled to populate the grid, as above)
This should work as well,
where the names: Name, Num and Mon
are defined ranges created via:
Insert Name Create (Top row)
in Sheet1
Think the latter version would be what you tried to do as per your original
post
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
|