How Excel displays the same data in Sheet1 and sheet2 and changes the format of the same data in She

Filter out the same data as column A in Sheet1 and Column A in Sheet2 and automatically modify the background highlighting? First, use the formula to import column A in sheet2 into any empty column in sheet1 (for example, aa in sheet1 is an empty column), and enter the formula =sheet2!a1 pulls down to fill.

Now select column A in Sheet1 and click Format (O) [Conditional Formatting (D)....In the conditional formatting window, select [Cell Value] as [Formula] under [Condition 1(1)], enter the formula =countif($aa$1:$aa$160,a1)>0 in the right long box, and click [Format (f)..

Pattern: Select the color you want to highlight, click OK, and then click OK. (Note: For the 2007 version of conditional formatting, please refer to the 2007 conditional formatting to set the same conditional formula), and now hide the aa column in the sheet1 table.

Enter the formula in b1 of sheet1: =if(a1="","",(vlookup(a1,sheet2!$a$1:

b$160,2,0))) Pull down the fill. Note: $aa$1 in the formula:

AA$160 with sheet2!$a$1:$b$160 Please adjust according to your specific needs.

Now see if you get it all!

Enter the formula in B1 of Sheet1: =VlookUp(A1, Sheet2!a:b,2,0).

1. Open the ** that needs to be set in the middle of the computer desktop.

2. After opening **, select the formatted area in sheet1.

3. Then click on the Start option in the toolbar.

4. Find the format painter in the opening directory and click on it.

5. Then click on sheet2 in **, and then switch to sheet2 workbook.

6. Select the area in the sheet2 workbook, and then click the format painter.

7. In this way, the format in sheet2 is the same as that in sheet1.

You can associate two **s.

Here's how:

1.First, build a green** framework that doesn't populate the data.

2.Select the data you want to copy in White.

3.Copy the data on Ctrl+C on your keyboard.

Then select the green ** where you want to fill in the data.

4.Ctrl+V on the keyboard to paste the data.

Select the last item "Link Cell" in the paste option in the bottom right corner

5.Well, give it a try. Modify the math score of 2012 in white ** to 100 points, and look at the results in green ** have also changed.

You enter the formula: =sheet1!, in h5 of the sheet2 tabled3

The number of H5 in Sheet2 will be the same as D3 in Sheet1.

You can only change it in one table. The other table can only be equal to the previous one.

There should be a name for each group in columns A-C in front of the landlord.

There are two simple ways to do this:

1.Directly Ctrl+Left Mouse Button, then pull right, copy Sheet1, then select Columns F-V, right click to delete the whole column, and then enter the formula =Sheet1!. in the fifth row cell (E5) in Column E of the copied worksheetw5 (or directly enter "=" and click the w5 cell of sheet1), then pull down to copy the cell;

Advantages: Simple operation.

Disadvantages: If the order of each group in sheet1 is changed, it will not correspond correctly.

2 For example, the first group is in the fifth row, column C, and the number of people is 50

Directly copy the A-D columns from Sheet1 to Sheet2, and then enter :

vlookup(c5,sheet1!$c$5: $w$390,21,0), where c5 represents the cell where the first group name is located, if the first group name is in cell b5, then $c$5 should be changed to $b$5, 21 should be changed to 22, and the cell where the first group name is located is a5, then $c$5 should be changed to $a$5, and 21 should be changed to 23

Advantages: No matter how the order of the group names is changed in the future, the score value in sheet2 is correct, and there is no need to readjust, once and for all.

Cons: The operation is slightly complicated.

With the formula in.

Write in the cells in sheet2 that you want to correspond to the cells in sheet1"=", then use the mouse to select a cell in the sheet, and finally press enter.

Write the formula in the corresponding row of column e of sheet2: for example, in cell e3: =sheet1!w3, drag down first.

Enter = in the e4 of sheet2 first, then click the sheet1 table label below ** with the mouse, find the w4 cell, click it with the mouse, then press enter or click on the left side of the bar, and then point the mouse to the lower right of the e4 cell of sheet2, and when the cursor becomes +, press the left mouse button and drag it down, and the following formula will come out.

Select a cell in column e in sheet2, enter =, wait for sheet1**, select the corresponding cell, press enter, that is, the effect upstairs!

You can use the vlookup function to implement two excels** to automatically match the content in sheet1 to sheet2 based on the same content in sheet1 and sheet2.

Software version: Office 2013

Here are some examples:

There are the following:

2.Enter the following formula in sheet2:

3.Drop down and fill in the formula, and you can get the data corresponding to the same data in sheet1 in sheet2.

There are two ways to do this.

It is suitable for situations where there are not many values。For example, in your case, the corresponding text appears in 1-6.

Enter at B1.

if(a1=1,"a",if(a1=2,"B",if(a1=3,"C",if(a1=4,"Ding",if(a1=5,"E",if(a1=6,"f","")))

2. It is suitable for the situation that there are many retrieval or the extracted text needs to be changed frequently.

Use the vlookup function.

In columns e and f, enter the pre-retrieved function and text.

Enter =vlookup(a1,e:f,2,0) in b1 for the following example:

Simple application of vlookup, enter the formula in b2 of sheet1:

vlookup(a1,sheet1!$a$1:$b$6,2,)

Enter =vlookup(a1,sheet1!) in cell b1 in table 2a:b,2,0)

Using formulas will cause circular references!

Want to use VBA**?

There may be a data link in sheet2 that references sheet1, and you need to find out the relationship between them.

The data in sheet1 should be referenced in sheet2. That is, the result in sheet2 is determined by the data in sheet1, so that the data in sheet1 will inevitably change the data in sheet2.