Column Content

[adam]

Hi,

In my workbook;

Cell O6 of "previous" sheet has 1111. Cell N18 of the same sheet has 40. Cell B5 of the sheet "Order" has 1111 and cell F5 of the same sheet has 40.

How could I write a code that would change the value in cell F5 of the sheet "Order" from 40 to 50 when the user changes the value in N18 from 40 to 50.

I hope I've made my question clear.

Any help on this would be kindly appreciated.

Thanks in advance.

[sdckapr]

in F5 you could have the formula
=N18
and they will be linked

Steve

[adam]

Thanks for the reply Steve. To understand my problem better; I've attached a sample workbook.

The sheet "Previous" sheet contains the serial number 1111 in cell O6. Column F contains the product code. And the column M contains product rate.

My question of concern is;

How to change the rate, highlighted in yellow in the sheet "Orders"to 40.00 when the user changes the cell M18 in the "Previous" sheet to 40.00 with the help of a VBA code?

In the same way if the user changes the rate for tomatoes from 20.00 to 25.00 in the "Previous" sheet and click the macro button; the related data to be changed in the "Orders" sheet.

In short; the product rate to get changed from the orders sheet(in reference to the serial number and product code) when the user changes the column content of "M" from the "Previous" Sheet.

Any help on this would be kindly appreciated.

[sdckapr]

In Orders!F5 enter the formula:
=VLOOKUP(C5,Previous!$F$18:$M$19,8,0)

And copy it down the column. It will lookup the value of the code in the table.

Steve

[adam]

Thanks for the formula Steve. But your formula looks for only the code not the serial Number.

For example having the serial number 1111 in cell O6, if I change the rate for tomato from 20.00 to 30.00 in the previous sheet, all the rows containing tomato's rate gets changed no matter what serial number is in column B of the orders sheet.

How could the formula be limited to change the values from only the specific serial number that resides in the previous sheet's O6.

I hope I've made my question clear.

[sdckapr]

Your example does not distinguish or use the serial number in the list as far as I can tell.

I suggest in your "previous" sheet to include both the serial number and code in the rows and then lookup this with B5&C5. But unless you have a representative example where the serial number is relevant, I don't see how an example formula could be created...

Steve

[adam]

My serial number does exist in cell O6 of the "previous" sheet. and the same serial number does exist in column B of the orders sheet. If I place the serial number in the same row of the previous sheet where data rows exits the format of the sheet would be changed.

I've tried as follows;
=IF(B5&C5="","",VLOOKUP(C5,Previous!$F$18:$O$37, 8,0))
But cannot get the formula working.

Any help on this would be kindly appreciated.

[sdckapr]

I know the serial number was in Previous!O6, it just isn't needed to extract out the data you asked for.

Can you attach a sample workbook of a setup that requires the serial number or what you changed it to...

Steve

[adam]

Attached please find the sample workbook.

[adam]

Instead of O6 in previous sheet I have placed the serial number in row E of the sheet previous. Now the serial number is in row E18 and E19. with this scenerio how could I use vlookup for multiple occurrences of the same value?

Any help on this would be kindly appreciated.

[adam]

Attached please find the latest version of the workbook.

[sdckapr]

You can use an array formula (confirm with ctrl-shift-enter) like
=INDEX(Previous!$M$18:$M$19,MATCH(B5&"|"&C5,Previous!$E$18:$E$19&"|"&Previous!$F$18:$F$19,0))

You could get by without an array formula if you had in Previous a cell that combined the serial and code together so it could be looked up directly.

Steve

[adam]

You could get by without an array formula if you had in Previous a cell that combined the serial and code together so it could be looked up directly.

Steve

I would be happy if you could let me know how to do the above mentioned.

Thanks in advance.

[adam]

I've shifted the column "serial" to column D and placed a formula in column E as =D18 & " "& F18 so that it shows up the combination of values from column E & column F. Is this what you had suggested?

Now the numbers in column E appears as 11112; when the serial is 1111 and code is 0002. If I'm right with this, how should I write a Vlook up formula then?

Any help on this would be kindly appreciated.

[sdckapr]

in F5:
=VLOOKUP(B5&" "&C5,Previous!$E$18:$M$19,9,0)

You will lookup the combined: B5&" "&C5 in the range Previous!$E$18:$M$19 and extract the 9th column (E=1, F=2, G=3,...,M=9). The zero at the end indicates that you want an exact match

Steve

[adam]

Thanks for the help. Works fine. but how could the #N/A be prevented from empty cells?

[HansV]

You can use the IFERROR function to suppress error values. See Excel IFERROR Function.

[adam]

Thanks for the help Hans. I've got it solved.

Meanwhile; how could I place the formulas in a worksheet module so as ;

=IF( ISERROR(VLOOKUP(B5&" "&C5,Previous!$E$18:$O$37,9,0)), "", VLOOKUP(B5&" "&C5,Previous!$E$18:$O$37,9,0) )
gets placed in column F

The following in column G
=IF( ISERROR(VLOOKUP(B5&" "&C5,Previous!$E$18:$O$37,9,0)), "", VLOOKUP(B5&" "&C5,Previous!$E$18:$O$37,10,0) )

The following in column H
=IF( ISERROR(VLOOKUP(B5&" "&C5,Previous!$E$18:$O$37,9,0)), "", VLOOKUP(B5&" "&C5,Previous!$E$18:$O$37,11,0) )

as data gets entered into Orders sheet from columns A to I simultaneously.

[sdckapr]

Copy the formulas you want them in row 5. Then copy the formulas down the columns to apply to the other rows...

Steve

[adam]

Thanks for the reply Steve. But that increases the workbook size. My intention is to copy the formulas as data rows get added.

Any help would be appreciated.