Hi everyone....
In Excel we have data in sheet 1 as follows

A B C D E
1 Index No. Date Blank Code
Reference
2 1208-2134706406 12/19/2008 0100 G
11/11/08-12/12/08
3 1208-2134706406 12/19/2008 0100 E
11/11/08-12/12/08
4 1208-2134726210 12/18/2008 0100 G
11/11/08-12/12/08
5 1208-2134726210 12/18/2008 0100 E
11/11/08-12/12/08

F G H I
J
1 Relation Relation code Amount Discount net
amount
2 R 70003-00 1,256.32 0.00
1,256.32
3 R 70002-00 1,755.64 0.00
1,755.64
4 R 70003-00 500.76 0.00
500.76
5 R 70002-00 780.75 0.00
780.75

K L
1 R. Date Target
2
3 12/23/2008 1223
4 12/24/2008 4558
5


In Sheet 2, we have to fill up data specifically for Reference starting with
"E"
A B C D E F
1 Index No. Start Date End Date Blank Blank
Target
2 1208-2134706406
3 1208-2134726210



In Sheet 3, we have to fill up data specifically for Reference starting with
"G"

A B C D E F
1 Index No. Start Date End Date Blank Blank
Target
2 1208-2134726210
3 1208-2134706406


The Sheet 2 & 3 are specific, the Sheet 2 contains details for Reference
starting with E, And Sheet 3 contains details of Reference starting with G.
In Column E, in sheet 1, €˜Reference, has details such as Reference (E or G)
and the start date and end date separated by €˜-€˜.

All we need is a V look up formula, to automatically fill in Column F in
Sheet 2 & 3 respectively for Reference (E & G) then Start date in Column B,
and End date in Column C.

We tried the following formula, but could not work properly.
=VLOOKUP($A$2:$A$3,Sheet1!$A$1:$L$5,12,0)

The other data that we tried was having If Condition i.e.
=IF(LEFT(Sheet1!E2,2="E "),VLOOKUP(Sheet2!A2:A3,Sheet1!A1:L5,12,0),"No
target")

Please help me.
Thanks.

You can apply a custom filter to column E of Sheet1 - Begins With E or
Begins With G - and then copy the visible data from column A to A2
(onwards) of Sheet2 or Sheet3, as appropriate. Then you can use these
formulae in B2 and C2 of Sheets 2 and 3:

B2: =MID(VLOOKUP(A2,Sheet1!A$2:E$5,5,0),3,8)*1
C2: =RIGHT(VLOOKUP(A2,Sheet1!A$2:E$5,5,0),8)*1

Format the cells as Date in the style you prefer (otherwise you will
get numbers like 39794).

Hope this helps.

Pete

On Dec 29, 9:53*am, K****ij wrote:
Hi everyone....
In Excel we have data in sheet 1 as follows

* * * * A * * * * * * * * * * * B * * * * * * * * * C * *D * * * * * * E *
1 * * * * Index No. * * * * * * * * * * Date * * * * * * *Blank * * *Code * *
Reference *
2 * * * * 1208-2134706406 * * * * 12/19/2008 * * * * * * * 0100 * * *G
11/11/08-12/12/08
3 * * * * 1208-2134706406 * * * * 12/19/2008 * * * * * * * 0100 * * *E
11/11/08-12/12/08
4 * * * * 1208-2134726210 * * * * 12/18/2008 * * * * * * * 0100 * * *G
11/11/08-12/12/08
5 * * * * 1208-2134726210 * * * * 12/18/2008 * * * * * * * 0100 * * *E
11/11/08-12/12/08

* * * * * * * * F * * * * * * * * * *G * * * * * * * * * H * * * * * * * *I *
* * * * * * * *J
1 * * * * Relation * * * Relation code * * *Amount * * *Discount * * net
amount
2 * * * * * * *R * * * * * * 70003-00 * * * * *1,256.32 * * * 0.00 * * * * *
1,256.32
3 * * * * * * *R * * * * * * 70002-00 * * * * *1,755.64 * * * 0.00 * * * * *
1,755.64
4 * * * * * * *R * * * * * * 70003-00 * * * * * * 500.76 * * * 0.00 * * * * *
* * 500.76
5 * * * * * * *R * * * * * * 70002-00 * * * * * * 780.75 * * * 0.00 * * * * *
* * 780.75

* * * * * * * * K * * * * * * * * L
1 * * * *R. Date * * * * *Target
2
3 * * * *12/23/2008 * *1223
4 * * * *12/24/2008 * *4558
5

In Sheet 2, we have to fill up data specifically for Reference starting with
"E"
* * * * * A * * * * * * * * * * B * * * * * * * * *C * * * * * * * * D * * * * *E * * * * * F
1 * *Index No. * * * * * * * *Start Date * * *End Date * * *Blank * * Blank *
*Target
2 * *1208-2134706406
3 * *1208-2134726210

In Sheet 3, we have to fill up data specifically for Reference starting with
"G"

* * * * * A * * * * * * * * * * B * * * * * * * * *C * * * * * * * * D * * * * *E * * * * * F
1 * *Index No. * * * * * * * *Start Date * * *End Date * * *Blank * * Blank *
*Target
2 * *1208-2134726210
3 * *1208-2134706406

The Sheet 2 & 3 are specific, the Sheet 2 contains details for Reference
starting with E, And Sheet 3 contains details of Reference starting with G.
In Column E, in sheet 1, ‘Reference’, has details such as Reference (E or G)
and the start date and end date separated by ‘-‘.

All we need is a V look up formula, to automatically fill in Column F in
Sheet 2 & 3 respectively for Reference (E & G) then Start date in Column B,
and End date in Column C.

We tried the following formula, but could not work properly.
=VLOOKUP($A$2:$A$3,Sheet1!$A$1:$L$5,12,0)

The other data that we tried was having If Condition i.e.
=IF(LEFT(Sheet1!E2,2="E "),VLOOKUP(Sheet2!A2:A3,Sheet1!A1:L5,12,0),"No
target")

Please help me.
Thanks.

Hi,

I have not read your mail very carefully but there seems to be a problem
with the VLOOKUP() formula. In the VLOOKUP() formula, the lookup value can
be one cell only, therefore your VLOOKUP() formula should be:

VLOOKUP($A$3,Sheet1!$A$1:$L$5,12,0) or VLOOKUP($A$2,Sheet1!$A$1:$L$5,12,0)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"K****ij" wrote in message
...
Hi everyone....
In Excel we have data in sheet 1 as follows

A B C D E
1 Index No. Date Blank Code
Reference
2 1208-2134706406 12/19/2008 0100 G
11/11/08-12/12/08
3 1208-2134706406 12/19/2008 0100 E
11/11/08-12/12/08
4 1208-2134726210 12/18/2008 0100 G
11/11/08-12/12/08
5 1208-2134726210 12/18/2008 0100 E
11/11/08-12/12/08

F G H I
J
1 Relation Relation code Amount Discount net
amount
2 R 70003-00 1,256.32 0.00
1,256.32
3 R 70002-00 1,755.64 0.00
1,755.64
4 R 70003-00 500.76 0.00
500.76
5 R 70002-00 780.75 0.00
780.75

K L
1 R. Date Target
2
3 12/23/2008 1223
4 12/24/2008 4558
5


In Sheet 2, we have to fill up data specifically for Reference starting
with
"E"
A B C D E F
1 Index No. Start Date End Date Blank
Blank
Target
2 1208-2134706406
3 1208-2134726210



In Sheet 3, we have to fill up data specifically for Reference starting
with
"G"

A B C D E F
1 Index No. Start Date End Date Blank
Blank
Target
2 1208-2134726210
3 1208-2134706406


The Sheet 2 & 3 are specific, the Sheet 2 contains details for Reference
starting with E, And Sheet 3 contains details of Reference starting with
G.
In Column E, in sheet 1, €˜Reference, has details such as Reference (E or
G)
and the start date and end date separated by €˜-€˜.

All we need is a V look up formula, to automatically fill in Column F in
Sheet 2 & 3 respectively for Reference (E & G) then Start date in Column
B,
and End date in Column C.

We tried the following formula, but could not work properly.
=VLOOKUP($A$2:$A$3,Sheet1!$A$1:$L$5,12,0)

The other data that we tried was having If Condition i.e.
=IF(LEFT(Sheet1!E2,2="E "),VLOOKUP(Sheet2!A2:A3,Sheet1!A1:L5,12,0),"No
target")

Please help me.
Thanks.