Alrite ppl I hope I am able to explain the problem I am facing here.. I
am working with multiple worksheets... what I want is to display
certain data in one worksheet by looking up the data from another
worksheet based on certain criteria ... let me explain - consider this
data to be present in a worksheet called 'PO Data' -

p/o no part no qty due date week no
1123 1195c 500 7/18/2006 2
1198h 1000 7/21/2006 2
1590dc 750 7/26/2006 3
1100-p 80961 200 7/4/2006 1
80961 500 7/20/2006 3
80961 30 7/21/2006 3

Now I have another worksheet say 'Plan' which SHOULD contain the
following data ..

part no qty due date week 1 week 2 week 3 week 4 week 5
1195c 500 7/18/2006 500
1198h 1000 7/21/2006 1000
1590dc 750 7/26/2006 750
80961 1200 7/4/2006 1200
80961 1500 7/20/2006 1500
80961 30 7/21/2006 30

So u see I want the data in the PLAN worksheet be displayed in the
respective columns when I enter the part no. I tried using VLOOKUP with
IF function but could not come up with a solution. I have used this
formula to calculate the week no =IF(WEEKDAY(A1 ,2)5, "", INT((DAY(A1)
+ WEEKDAY(A1-DAY(A1) + 2) + 3)/7)) ... I also tried to use the same in
the 'Plan' worksheet but could not come up with anything... can
someone please help me with this?

adi

Adi, it looks like the PO Data sheet is your source and your just
plugging in the part number in the A column of the Plan worksheet and
want it to populate everything from PO Data to Plan worksheet for that
part.

If thats true then you need to put the "Part No" Column (Column B) into
Column A. Just reverse it with P/O No. VLookup requires the lookup
field to be the leftmost entry in the range you are looking at. If you
dont want to do that and dont need the P/O No, just start your vlookup
range in column B.

Example for cell B2 in Plan Worksheet:
=VLOOKUP(A2,PO DATA!A1:E20,3,FALSE)

If A2 was 1195c, it would return 500 for the quantity. Do the same for
all other columns but increment the Column Index number to return the
correct column value.

Hope this helps


--
jtp
------------------------------------------------------------------------
jtp's Profile: http://www.excelforum.com/member.php...o&userid=21132
View this thread: http://www.excelforum.com/showthread...hreadid=562682