How to look at two sheets and auto correct the cell content

dsgbob · Mar 4, 2009

PLEASE HELP

I have two sheets;

1 is a Master sheets with all prices of products etc etc.
2nd sheet is an update of all products that have changed in price.

There is a unique code for each product.

What i was hoping for is a way where on the second sheet the product (using the unique codes assigned to the product) is looked up on the master sheet and the NEW price on the 2nd sheet replaces the OLD price on the Master sheet?

Thanks
BOB @ downsouth

Yard · Mar 4, 2009

To do that with formulas I think you would need another column on your Master sheet (adjacent to your current prices would be sensible).

Assuming your current codes and prices are in master sheet columns A & B (starting here at A2), and your new codes & prices are on Sheet2!A1:B10, then try this in the master sheet cell C2:

=IF(COUNTIF(Sheet2!$A$1:$B$10,A2),VLOOKUP(A2,Sheet2!$A$1:$B$10,2,0),B2)

This formula checks if the code number on the master sheet (in A2) is listed in the new prices list. If it is, then it returns up that new price by looking it up via the code number. If it isn't, it just returns the existing price (in B2).

dsgbob · Mar 4, 2009

Sorry for being oblivious but if i break it down to you where the exact content is:

Master sheet:

Unique Code starts in cell D9 all the way down to D1600
Original Price starts in cell M9 all the way down to D1600

New Price Sheet showing Price diffrences fom wholesaler (only 200 odd products change) :

Unique Code starts in cell C2 all the way down to C205
New Price starts in cell J2 all the way down to J205

and the code would autmoatically replace the original price with the new price?

Thanks

Yard · Mar 4, 2009

OK, so try this in your master sheet (in row 9, any column, then copy it down to row 1600):

=IF(COUNTIF(NewPriceSheet!$C$2:$C$205,D9),VLOOKUP(D9,NewPriceSheet!$C$2:$J$205,8,0),M9)

dsgbob · Mar 4, 2009

ersonal; mso-style-noshow:yes; mso-style-unhide:no; mso-ansi-font-size:11.0pt; mso-bidi-font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; color:windowtext;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> You are a star!!

it works on a new column

Can i ask for one more thing if i place the coding into the original price cell M9 it says circular error...the reason im doing this is so it’s all automated so it automatically changes the original price column of there is....is there a way?

Or if not and i have to manually put the changed data into the original price column, can you tell me how excel could automatically highlight your coding to show these are the price changes? so i can easily pick notice the 200 price changes?<o

></o

>

Yard · Mar 4, 2009

dsgbob said:
<META content=Word.Document name=ProgId><META content="Microsoft Word 12" name=Generator><META content="Microsoft Word 12" name=Originator><LINK href="file:///C:%5CUsers%5Ckrishan%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml" rel=File-List><LINK href="file:///C:%5CUsers%5Ckrishan%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx" rel=themeData><LINK href="file:///C:%5CUsers%5Ckrishan%5CAppData%5CLocal%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml" rel=colorSchemeMapping><STYLE>  </STYLE> You are a star!!

it works on a new column

Can i ask for one more thing if i place the coding into the original price cell M9 it says circular error...the reason im doing this is so it’s all automated so it automatically changes the original price column of there is....is there a way?

Or if not and i have to manually put the changed data into the original price column, can you tell me how excel could automatically highlight your coding to show these are the price changes? so i can easily pick notice the 200 price changes?<?xml:namespace prefix = o /><o></o>

Of course you get a circular error in M9 - imagine being poor old cell M9..."if the code isn't in the new price list, then take the current price which is, erm, me...so to work out what I am, I need to check if the code is in the new price list...oh, it isn't so I'll take the current price which is, erm, me...so to work out what I am, I need to check if the code is in the new price list...oh, it isn't so I'll take the current price which is, erm...."

Definitely a good idea to use Conditional Formatting to highlight the values that differ. You could do this either in your new column or on your master prices.

Highlight the cells you want (I'll assume master prices in M9:M1600 and your new formula in column Z), go to Conditional Formatting, put Condition 1 as

Cell value not equal to =Z9

and format it how you want.

You get the idea.....

OR

since you have rather a lot of values to search for, maybe another column would help which identifies if the prices are different, like:

=M9=Z9

this will return FALSE for those values which are different. Now you can use an Autofilter on that column, filter all the FALSE rows and you have a nice list of the prices which are different.

HTH

dsgbob · Mar 4, 2009

Thank you so much for this, but im bad at Excel

i have excel 2007 when i go to conditional formatting i really dont which option im meant to be using?

Yard · Mar 4, 2009

Select area M9:M1600.

Click Conditional Formatting > New Rule

Choose Format only cells that contain

Now choose :

Cell value

then

not equal to

then in the next box (which is blank), enter

=Z9 (where Z is the column containing your new formula)

Then click Format....

Choose your formatting.

Click OK.

If you want to amend it later, make sure your active cell is within M9:M1600, go to Conditional Formating > Manage Rules, click the rule and then Edit.

dsgbob · Mar 14, 2009

Thanks so muc for the help, can i ask if the sheet has been amended and now looks like this:....

Master sheet:

Unique Code starts in cell G9 all the way down to G1600
Original Price starts in cell M9 all the way down to M1600

New Price Sheet showing Price diffrences fom wholesaler (only 200 odd products change) :

Unique Code starts in cell A5 all the way down to A205
New Price starts in cell K5 all the way down to K205

should the formula not be:

=IF(COUNTIF(Sheet2!$A$5:$A$1900,G9),VLOOKUP(G9,Sheet2!$A$5:$K$1900,8,0),M9)

it does not give the correct value?

Thanks

Yard · Mar 16, 2009

Not sure why you've included the range down to row 1900 in your new formula? I think you want this in row 9 in your Master sheet:

=IF(COUNTIF(NewPriceSheet!$A$5:$A$205,G9),VLOOKUP(G9,NewPriceSheet!$A$5:$K$205,11,0),M9) 

Anyway, here's an explanation of what's happening so you can modify it again in the future.

=IF(COUNTIF(NewPriceSheet!$A$5:$A$205,G9),VLOOKUP(G9,NewPriceSheet!$A$2:$K$205,11,0),M9)

COUNTIF(NewPriceSheet!$A$5:$A$205,G9)
This counts how many times the value in G9 exists in the range A5:A205 on the NewPriceSheet.

VLOOKUP(G9,NewPriceSheet!$A$5:$K$205,11,0)
This takes the value in G9, looks for it in the first column of the range A2:K205 and then returns whatever value is 11 columns to the right of there. Read the Help on VLOOKUP, it explains further. I guess you were getting the wrong answer is because you were still looking 8 columns across, which was no longer the right place to look.

The IF(...,...,M9) statement simply says:
If the COUNTIF function returns a value>0 (i.e. it equates to TRUE, where 0=FALSE and any other number equates to TRUE), then return the result from the VLOOKUP. Otherwise return whatever value is in M9.

Hope that helps.

How to look at two sheets and auto correct the cell content

dsgbob

New Member

Excel Facts

Yard

Well-known Member

dsgbob

New Member

Yard

Well-known Member

dsgbob

New Member

Yard

Well-known Member

dsgbob

New Member

Yard

Well-known Member

dsgbob

New Member

Yard

Well-known Member

Similar threads

Forum statistics

Share this page

How to look at two sheets and auto correct the cell content

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock