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

#### dsgbob

##### New Member

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

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### Yard

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### New Member

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

##### Well-known Member
<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> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:0; mso-generic-font-family:roman; mso-font-pitch:variable; mso-font-signature:-1610611985 1107304683 0 0 159 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; 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; mso-fareast-language:EN-US;} span.EmailStyle15 {mso-style-typeersonal; 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?<?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

##### New Member
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

##### Well-known Member
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....

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

##### New Member
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

##### Well-known Member
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) <!-- / message --><!-- sig -->

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.

Replies
1
Views
357
Replies
4
Views
170
Replies
22
Views
2K
Replies
3
Views
213
Replies
9
Views
448

1,191,308
Messages
5,985,904
Members
439,986
Latest member
DaveTee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back