Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 2 criteria across multiple worksheet (Attn: Peo Sjoblom)
The formulae provided by Peo Sjoblom in response to Ray Stubblefield question
Lookup across multiple worksheets is a masterpiece. I used the formulae provided by Peo Sjoblom to solve my problem (but not successful), which is as follows - ( I want to create a summery sheet, for 27 sheets I used in the workbook) Col 1 Col 2 Col 3 Col 4 Col 5 Voucher No Cheque No Date of Issuing Cheque Sanction Order No Amount 1 698707 Col 1 : It contains Numeric ascending sorted Value 1 to 99 which is manually entered this is one of the criteria based on which values in other column are extracted to the respective other columns. Col 2 : This column use this formulae =VLOOKUP(A6,THREED('1:27'!$J$9:$L$99),3,0) But I want to extract the cheque no based on 2 criteria 1. Voucher No (Which is in col 1 a numeric value i.e. 99) 2. Voucher Type in my case it is Bank Payment which is sheet name 1 to 27 Col K. My Problem : the formulae given above search only based on 1 Criteria (A6 as shown in formulae) i.e. Voucher No, But I need to see also voucher no are from Bank Payment or not these are mentioned in the sheets (1:27) columns in following order Col J = Voucher No (Numeric value 1 to 99) Cil K = Voucher Type (only 2 type are there Bank Payment & Journal) Col L = Cheque No (6 Digit Numeric value) Col 3, 4, 5 : In those columns, based on the cheque no I want to search in all sheets and extract value here. My Problem : Here my problem is all the value is not in contagious cell. And search criteria value is in last column of the array. In each sheets value are in following cell Cheque No = Col L (search Criteria) Date of Issuing Cheque = Col A Sanction Order No = Col E Amount = Col G any help help in this regard will be appreciated, looking forward for responses. Thanks in advance. Rajat Sys Detail : Win XP SP2, 512 MB RAM, Excel 2003 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 2 criteria across multiple worksheet (Attn: Peo Sjoblom)
In the interim .. perhaps consider an alternative set-up using standard
worksheet functions that would deliver the results that you're after ? Illustrated in this sample: http://www.flypicture.com/download/MzgyMTQ= Lookup multi-crit across multi shts.xls It's presumed that the cheque nos to be extracted are unique, ie based on the multi-criteria, they will be found in only 1 of the 27 source sheets, if at all Source data is assumed within rows 2 to 100 in each of the 27 identically structured source sheets, named as: 1,2,3, ... 27 In your summary sheet, Voucher #'s are assumed in A6 down to say, A20 List the 27 sheetnames across in M5:AM5, viz: 1,2,3,.... 27 Then put in M6, array-enter the formula, ie press CTRL+SHIFT+ENTER: =INDEX(INDIRECT("'"&M$5&"'!L2:L100"),MATCH(1,(INDI RECT("'"&M$5&"'!J2:J100")=$A6)*(INDIRECT("'"&M$5&" '!K2:K100")="Bank Payment"),0)) Copy M6 across to AM6, fill down to last row of voucher# expected in col A, ie to AM20 Put the labels in B5:F5 : In Tab, Cheque No, Date of Issuing Cheque, Sanction Order No, Amount Then array-enter, ie press CTRL+SHIFT+ENTER the following In B6: =IF($A6="","",INDEX($M$5:$AM$5,MATCH(TRUE,ISNUMBER (M6:AM6),0))) In C6: =IF($A6="","",INDEX(M6:AM6,MATCH(TRUE,ISNUMBER(M6: AM6),0))) Normal ENTER the following In D6: =IF($A6="","",INDEX(INDIRECT("'"&$B6&"'!A:A"),MATC H($C6,INDIRECT("'"&$B6&"'!L:L"),0))) In E6: =IF($A6="","",INDEX(INDIRECT("'"&$B6&"'!E:E"),MATC H($C6,INDIRECT("'"&$B6&"'!L:L"),0))) In F6: =IF($A6="","",INDEX(INDIRECT("'"&$B6&"'!G:G"),MATC H($C6,INDIRECT("'"&$B6&"'!L:L"),0))) Then just select B6:F6, copy down to F20. Cols C to F returns the results that you're after. Col B returns the tab/sheetname that the cheque is found in. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rajat" wrote: The formulae provided by Peo Sjoblom in response to Ray Stubblefield question Lookup across multiple worksheets is a masterpiece. I used the formulae provided by Peo Sjoblom to solve my problem (but not successful), which is as follows - ( I want to create a summery sheet, for 27 sheets I used in the workbook) Col 1 Col 2 Col 3 Col 4 Col 5 Voucher No Cheque No Date of Issuing Cheque Sanction Order No Amount 1 698707 Col 1 : It contains Numeric ascending sorted Value 1 to 99 which is manually entered this is one of the criteria based on which values in other column are extracted to the respective other columns. Col 2 : This column use this formulae =VLOOKUP(A6,THREED('1:27'!$J$9:$L$99),3,0) But I want to extract the cheque no based on 2 criteria 1. Voucher No (Which is in col 1 a numeric value i.e. 99) 2. Voucher Type in my case it is Bank Payment which is sheet name 1 to 27 Col K. My Problem : the formulae given above search only based on 1 Criteria (A6 as shown in formulae) i.e. Voucher No, But I need to see also voucher no are from Bank Payment or not these are mentioned in the sheets (1:27) columns in following order Col J = Voucher No (Numeric value 1 to 99) Cil K = Voucher Type (only 2 type are there Bank Payment & Journal) Col L = Cheque No (6 Digit Numeric value) Col 3, 4, 5 : In those columns, based on the cheque no I want to search in all sheets and extract value here. My Problem : Here my problem is all the value is not in contagious cell. And search criteria value is in last column of the array. In each sheets value are in following cell Cheque No = Col L (search Criteria) Date of Issuing Cheque = Col A Sanction Order No = Col E Amount = Col G any help help in this regard will be appreciated, looking forward for responses. Thanks in advance. Rajat Sys Detail : Win XP SP2, 512 MB RAM, Excel 2003 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 2 criteria across multiple worksheet (Attn: Peo Sjoblom
Illustrated in this sample:
http://www.flypicture.com/download/MzgyMTQ= Lookup multi-crit across multi shts.xls If you're reading the above in MS' webpage, don't click directly on the link. It'll bring you to the wrong page. Just copy the entire link, inclusive of the trailing "=", and paste into the browser's address bar, press ENTER. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 2 criteria across multiple worksheet (Attn: Peo Sjoblom
Hi Max
Nice solution!! -- Regards Roger Govier "Max" wrote in message ... Illustrated in this sample: http://www.flypicture.com/download/MzgyMTQ= Lookup multi-crit across multi shts.xls If you're reading the above in MS' webpage, don't click directly on the link. It'll bring you to the wrong page. Just copy the entire link, inclusive of the trailing "=", and paste into the browser's address bar, press ENTER. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 2 criteria across multiple worksheet (Attn: Peo Sjoblom
Roger,
Thanks for the compliments. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Roger Govier" <rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi Max Nice solution!! -- Regards Roger Govier |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 2 criteria across multiple worksheet (Attn: Peo Sjoblom
Hi Max,
thanx a lot for the solution and also for the link of the illustration. i'll try those, get back to you if any problem faced. Thanx again for the prompt response. regards Rajat |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 2 criteria across multiple worksheet (Attn: Peo Sjoblom
welcome, Rajat.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Rajat" wrote in message ... Hi Max, thanx a lot for the solution and also for the link of the illustration. i'll try those, get back to you if any problem faced. Thanx again for the prompt response. regards Rajat |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 2 criteria across multiple worksheet (Attn: Peo Sjoblom
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 2 criteria across multiple worksheet (Attn: Peo Sjoblom
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup 2 criteria across multiple worksheet (Attn: Peo Sjoblom
Since the file is quite calc-intensive, it might be appropriate here to set
the calc mode to Manual (under Tools Options Calculation tab) so as not to hinder routine data entry in the source sheets. After data entry is complete, or whenever needed, just press F9 to recalc. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup using multiple sheets and multiple criteria, sorry if 2 pos | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) | |||
Lookup on multiple criteria and max | Excel Worksheet Functions | |||
Lookup with multiple criteria... please help! | Excel Worksheet Functions | |||
HELP!! Lookup multiple criteria | Excel Discussion (Misc queries) |