EXCEL citation problem, Excel citation is invalid, how to solve it

The two upstairs are correct, it's you yourself who don't understand :)

Are Table B and Table A two workbooks, i.e. two separate excel documents? If so, the name of the workbook that needs to be added to the formula is usually indicated in parentheses.

For example, in Sheet1 of your workbook table B, column A is the model number, and column B is the corresponding **.

Now you have to enter the model number in another file, which is sheet1 in table a, and automatically reference the ** in table b, if so, then the formula is going to change. Enter the formula in Cell B1 of Sheet1 of the table a file

vlookup(A1,[Table!] $a:$b,2,0)

Here's an example we gave, for the state I said earlier is valid, and the Hugh reference and query area in the formula should be written according to your **.

In addition, there is another point, the **** formed by this query, if you are an electronic version of the customs declaration when you clear the customs, you also have to have an operation, because your **table is quoted from your table b, so when you submit this ** report to a computer that can't find table b, the computer will prompt to update the source data. The solution to this problem is that after you form the customs declaration table, copy the whole table, and then selectively paste, value, this method removes all your formulas, and only retains the last result displayed in your **. This way, there will be no update issues as mentioned earlier.

c2=vlookup(A2, Table B!a:b,2,0)

C2 is the unit price cell, A2 is the model you entered, A:B is the range between the model and the unit price in Table B, and 2 is the number of columns in the range of the unit price!

Assuming that the model number of each component and the corresponding unit price are in column AB, the first line is the header, and the formula is entered in B2 of Table A

vlookup(A2, Table B!.)a:b,2,0)

Excel is a commonly used data processing software, and novices may encounter a variety of problems in the process of using hailToday, I will take you to learn to solve the problem of "invalid reference, the Sun Wang cell cited by the formula cannot be located in the 256 column" in Excel.

1. First of all, when using the region reference formula, such as the vlookup function, if you encounter an "invalid reference" error, as shown in the figure below.

2. This is because the data of excel2007 and later versions are cited in the excel2003 version, because the maximum row in 2003 is 66536 rows, and the maximum column is 256 columns, and the maximum row and column in 2007 far exceed this data, which will lead to such a problem, as shown in the following figure.

3. One way is to limit the rows and columns referenced in the formula to 256 columns and 65536 rows, as shown in the figure below.

4. Another way is to save the excel2003 version as the 2007 version, close it and open it again, as shown in the figure below.

5. Both methods can solve the problem of "invalid references", as shown in the following figure.

1. The ear next to the jujube is first opened in the excel**.

, after inserting the control, design mode.

It can't be selected, and the interface can't be clicked.

2. The reference is invalid prompt, and the software cannot be closed, as shown in the figure below.

3. At this time, you can press the keyboard shortcut Ctrl+Alt+Delete to guess and disassemble the task manager.

Select the excel process and click the End Task button.

4. Then right-click on the window and select Close Task Cavity Manager.

5. Finally, double-click the excel shortcut again.

Open the widget again is deleted, and no more pop-up prompts will be displayed.

1. First of all, in the open excel**, after inserting the control, the design mode cannot be selected, and the interface cannot be clicked.

2. The reference is invalid prompt, and the software cannot be closed, as shown in the figure below.

3. At this time, you can press the keyboard shortcut Ctrl+Alt+Delete to open the task manager, select the excel process, and click the end task button.

4. Then right-click on the window and select Close Task Manager.

5. Finally, double-click the excel shortcut again, open the control again and the control will be deleted, and the prompt box will not pop up again.

If your link address or file has been lost, and the reference data is updated, it will prompt that the reference is invalid and an error message will appear, then this process is irreversible. You can only close an article without saving it.

Hello dear, the reason for this issue is: Iterative calculations are not enabled. The solution and detailed operation steps are as follows:

1. First of all, open Excel on your computer, then click on the File option in the upper left corner, and then go to the next step. 2. Secondly, after completing the above steps, click on the option at the bottom left corner of the page in the pop-up file interface and proceed to the next step.

Then, after completing the above steps, find and click on the formula option as shown in the image below, and proceed to the next step. Then, after completing the above steps, look at the "Enable Iterative Calculations" option, click on the checkbox in front of you as shown in the image below, and proceed to the next step. Finally, after completing the above steps, click the "OK" button as shown in the image below.

In this way, the problem is solved.

It should be that you copied the entire worksheet from another workbook that contains the name. So, check the names in this workbook with annoying prompts. (Find it in the menu Insert - Define Name).

Word and Excel provide ways to synchronize Word with Excel data

Step 1: Open the excel sheet (here it is the source file) and the word document you want to reference the excel data, then select the area you want to reference in excel and "copy" to the clipboard;

Step 3: Go back to the excel sheet, enter the content in the referenced area, and then press enter, and the reference content in the word document will be changed synchronously immediately! Note: Remember to "Enter" after entering the content to quickly synchronize changes!

Finally, I would like to add: in practice, when you are a source file, of course, you can not open the referenced file at the same time, so that when you change the source file to save, when you open the file that references the source file, it will prompt whether it is updated with the source file, then you only need to select "y" or "n" to achieve the purpose we need!

The first case: call the data in another worksheet in the same excel file with the following two worksheets:

sheet1

sheet2

For example, in a certain grid in Sheet1, the data of the A1 grid in Sheet2 is displayed, and Method 1:

Fill in the box where you want to display data.

sheet1!a1

Method 2: Use the indirect() function, you can use this function to realize the difference of the data source ** and delete the line without affecting your formula, for more information hi me.

indirect("sheet1!a1"Method three:

With the offset() function, use offset(target, column, row,) = offset(sheet1!a1,0,0,) Method 4:

There are also many functions vlookup(), hlookup(), lookup(), index(), and so on.

You can use it, I won't list it here.

In the second case, data A. in another excel file is calledTwo files in the same folder.

File files. If you want to call the A1 grid data of sheet sheet1 in the file, write it like this.

File! a1b.If you want to use an absolute address.

For example, the address of the file is c: file.

Here's how the formula is written.

C: [Document.]'!a1

Suppose that the data in column a is of the same nature.

To reference the column (b) after the data that meets the conditions in table A to column B of table B, enter the formula: in b1 of table B

vlookup(a1,a!a:b,2,false) and copy the formula to the other cells in column b.

Then the value of the cell in column b is not equal to n a.