R
Rajat
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
“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