Here's a sample with all the thoughts expressed below implemented:
http://www.flypicture.com/download/Mzg5NzU=
Lookup multi-crit across multi shts v2.zip
.. Your formulae worked like wonder. It solved my problem.
That's good to hear ..
.. But in few of the cells #N/A is shown, but Ive checked and
found that all the value is there in specific sheets..
Ah, some sleuthing revealed that you had a very common source data
inconsistency problem. There was an extra trailing space entered together
with the text: €śBank Payment€ť for the 3 cases which should have matched
otherwise. TRIM can be used to handle this.
In sheet: BP,
Revised array formula in M6 with TRIM added for increased robustness of
matching:
=INDEX(INDIRECT("'"&M$5&"'!L9:L100"),MATCH(1,(INDI RECT("'"&M$5&"'!J9:J100")=$A6)*(TRIM(INDIRECT("'"& M$5&"'!K9:K100"))="Bank Payment"),0))
M6 re-copied across / filled down to populate.
I have another question, I want to create
another summary sheet using €śJournal€ť ...
This is actually a whole new ballgame, and the question should be asked in a
fresh, new posting. The earlier set up was good for the intents you had in
mind for sheet: BP, but it is not possible to apply the same here.
My thoughts were to dynamically gather data from all pertinent cols in the
28 source sheets into a single contiguous range in a new sheet. Then we could
easily use autofilter and pivot table to query/summarize it further ..
Here's how ..
In a new sheet: x
(This new sheet is for your "Details of Journal Vouchers issued to date")
Headers placed in A1:G1 :
Voucher #, In Tab, Cheque No, Date of Issuing Cheque,
Sanction Order No. & Date, Voucher Type, Monthly Amt
In A2:
=IF(OFFSET(INDIRECT("'"&INT((ROWS($1:1)-1)/92)+1&"'!J9"),MOD(ROWS($1:1)-1,92),)=0,"",OFFSET(INDIRECT("'"&INT((ROWS($1:1)-1)/92)+1&"'!J9"),MOD(ROWS($1:1)-1,92),))
In B2:
=INT((ROWS($1:1)-1)/92)+1
In C2:
=IF(OFFSET(INDIRECT("'"&INT((ROWS($1:1)-1)/92)+1&"'!L9"),MOD(ROWS($1:1)-1,92),)=0,"",OFFSET(INDIRECT("'"&INT((ROWS($1:1)-1)/92)+1&"'!L9"),MOD(ROWS($1:1)-1,92),))
In D2:
=IF(OFFSET(INDIRECT("'"&INT((ROWS($1:1)-1)/92)+1&"'!A9"),MOD(ROWS($1:1)-1,92),)=0,"",OFFSET(INDIRECT("'"&INT((ROWS($1:1)-1)/92)+1&"'!A9"),MOD(ROWS($1:1)-1,92),))
In E2:
=TRIM(OFFSET(INDIRECT("'"&INT((ROWS($1:1)-1)/92)+1&"'!E9"),MOD(ROWS($1:1)-1,92),))
In F2:
=TRIM(OFFSET(INDIRECT("'"&INT((ROWS($1:1)-1)/92)+1&"'!K9"),MOD(ROWS($1:1)-1,92),))
In G2:
=IF(F2="","",OFFSET(INDIRECT("'"&INT((ROWS($1:1)-1)/92)+1&"'!G9"),MOD(ROWS($1:1)-1,92),))
Select A2:G2, copy down to G2576 (to cover the 92* rows expected per sheet
in the 28 source sheets: 1 - 28). Cols A to G will automatically gather the
contents from the correct cols in all the source sheets, in the sequence of
the numeric tabs: 1 - 28. The first 92 rows will be data from sheet: 1, the
next 92 from sheet: 2, and so on till sheet: 28.
*92 was used to be consistent with the ranges you used in sheet: BP (row
9-row 100)
Then you could easily apply autofilter on all cols and in col F ("Voucher
Type"), just select "Journal" in the autofilter dropdown to view all the
journal records at one go. You could then subfilter further by the cheque no.
/ voucher# as may be required.
You could also create a pivot table based on cols A to G in this sheet: x to
facilitate summations by Voucher#, Cheque no. & Voucher Type. See the pivot
created in sheet: PT for one possibility.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rajat" wrote:
Hi Max,
Your formulae worked like wonder. It solved my problem. But in few of the
cell #N/A is shown, but Ive checked and found that all the value is there in
specific sheets. Please find the file in the following link €“ in the BP sheet
Ive mentioned in which sheet data is but cell is showing it as €ś#N/A€ť
http://www.flypicture.com/download/Mzg4NzE=
I have another question, I want to create another summery sheet using
€śJournal€ť as one of the search criteria instead of €śBank Payment€ť. But few
changes needs to be make which are as follows €“
Voucher # :
It will be manually entered, ascending order numeric list starting from 1 to
99.
In Tab :
Here entries may be found in more than 1 tab. Hence if it is possible to
show all the tabs where Voucher # have entries separated by comma i.e. 3,5,19
etc.
Cheque No / Date of Issuing Cheque / Sanction Order No & Date :
No change required from the solution provided earlier.
Amount :
Here I want to sum the amount. In the Journal voucher several sub-activities
(found in different Tab) are booked under one Cheque No, hence this cell
should sum all the sub-activities and show Total amount here.
Waiting for you response Max,
Regards
Rajat