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

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

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
---

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
---

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

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