Copy Contents

[adam]

Hi anyone,

In the attached workbook, how could I make the related date from the column “F” of the sheet “OrderedItems”; to get copied to the cell “O10” of the sheet “NewOrder” when I write the request number in cell I9?

Let’s say for example If I write 111/10 in the cell “I9” of the sheet NewOrder, I want the date 12/1/2010 to get copied to the cell “I10”

Any help on this would be kindly appreciated.

Thanks in advance.

[HansV]

Format the target cell as a date, and enter the following formula in this cell:

=IF(I9="","",VLOOKUP(I9,OrderedItems!E10:F45,2,FALSE))

[adam]

Thanks for the help Hans.

How could I make the appropriate content from the column “M” of the sheet “OrderedItems” to get copied to the column “O” of the sheet “NewOrder” as I write the item name in the column “K” of the sheet “NewOrder.

For example when I write the 111/10 in the cell “I9” the formula copies the date 12/1/2010 to the cell “I10”

Having the above data in appropriate cells; when I write “Chai” in column “K” I want the data row (3bottls ) from column “M” of the sheet “Ordered Items” to get copied into the column “O” of the sheet “NewOrder” that is related to the Order number in cell “I9”

[adam]

This modification solved the question.

=IF(I9="","",VLOOKUP(I9,OrderedItems!E10:M45,9,FALSE))

[adam]

Sorry! this modification does not seem to work up.

Lets say for example if the user writes "chang" in the column "K19" with the formula, the column "O19" still shows the amount "3Kit" instead of showing "6Kit".
So how could the above code be changed so that it shows the exact quantity ordered for the product.

[HansV]

You should use the code to look up an article, not the description.

Activate the OrderedItems sheet.
In cell C10, enter the formula

=G10&"/"&E10

Fill down to C45.
You can hide column C if you like.

Activate the NewOrder sheet.
In cell O18, enter the formula

=VLOOKUP(F18&"/"&$I$9,OrderedItems!$C$10:$N$45,11,FALSE)

Fill down to O35.

[adam]

You should use the code to look up an article, not the description.

I would be happy if you could define the above.

Meanwhile, the original data sheet in my workbook contains some other data in the column "C".

Having this situation; where should I place the formula (=G10&"/"&E10) which you have suggested to be put in column "C" of the sheet "OrderedItems"

Furthermore, the date in the OrdredItems starts from the Row 5 of column A and the serial number in column "B". With this situation the modification of the formula suggested in the first reply of this thread does not seem to work when the serial number is written in cell "I9" of the sheet NewOrder..

Here is how it had been modified;

=IF(I9="","",VLOOKUP(I9,OrderedItems!A5:B45,1,FALSE))

What may be the reason for this?

[HansV]

If you have moved the data to a different location, you will have to adjust the formulas accordingly.

[adam]

If you have moved the data to a different location, you will have to adjust the formulas accordingly.

As with the above suggestion the formula below does not work.

=IF(I9="","",VLOOKUP(I9,OrderedItems!A5:B45,1,FALSE))

What may I have done wrong here?

[HansV]

You cannot use VLOOKUP to look up a value to the left. You can use a combination of INDEX and MATCH instead.

[adam]

Because, as said in the in the post Post 32600 the date is in column "A" of the sheet "OrderedItems".

So, in the hope to make the formula workout I did try changing the column number (the next-to-last argument) to 1.

But it failed.

[HansV]

I edited my previous reply.

[adam]

Thanks for the suggestion Hans.

As per your suggestion the following formula has been created.

INDEX(OrderedItems!$A$5:$A$17,MATCH(I9,OrderedItems!$B$5:$B$17,0)) And the formula works fine.

But the modification of the formula as follows

=VLOOKUP(F18&"/"&$I$9,OrderedItems!$A$5:$I$17,8,FALSE) does not seem to work when its placed in the NewOrder Sheet.

I've placed the formula =C5&"/"&B5 in column "I" of the sheet OrderedItems.

What may be missing in here?

[adam]

Is this because the serial number format that I'm having is in the format 0001 instead of 111/10

[HansV]

Although an hour and a half have passed, VLOOKUP still can't lookup to the left.
You'll have to use a combination of INDEX and MATCH here too.

[adam]

I have tried the following by placing it in row 18 and onwards, but without success....

=INDEX(OrderedItems!$H$5:$H$17,MATCH(I9,OrderedItems!$B$5:$B$17,1))


Any help is much appreciated.

[HansV]

You have to use concatenation as in =VLOOKUP(F18&"/"&$I$9,OrderedItems!$C$10:$N$45,11,FALSE) but now with INDEX and MATCH.

[adam]

Thanks for the reply.