Sometimes you need to select and view values in a worksheet with huge amount of data and information. You can use the two methods in this article to finish this task.
Viewing values in a worksheet is necessary in your work. When there are a lot of data and information in the worksheet, you will find it difficult to finish this task.
In the image below, there are the sales volumes of many sales representatives.
You will easily make mistakes with such a big worksheet. And now we have such an idea: When you input the name into a cell, the corresponding sales volumes appear in other two cells. Thus, you can view values one by one easily and you will never make mistakes. To view values with this new range, you can read the following parts.
Arrange the Worksheet
Before you view the values, you need to create a new range.
- Input the same column header into another range. And here we will input the headers into range E1: G1.
- Now click the cell E2.
- And then click the tab “Data” in the ribbon.
- After that, click the button “Data Validation” in the toolbar.
- In the “Data Validation” window, click the small arrow in the text box of “Allow”.
- Next choose the “List” in the drop-down menu.
- Now input the source range A2:A31 into the corresponding text box. You can also use your mouse to directly choose the range.
- When you finish all the settings, click the button “OK” to save the setting.
And now the new area has been created. When you need to view the sales volume of a certain sales representative, you can select in the drop-down list that you have created. Besides, you can also directly input the name into the cell. With this data validation in the range, mistakes such as inputting the wrong name can be well circumvented. Next we will introduce the two methods in detail.
Method 1: Use Functions
In this method, you will use the Excel functions.
- Click cell F2 on the keyboard.
- And then input this formula into the cell:
- And then press the button “Enter” on the keyboard.
- Now still click the cell F2.
- Click the fill handle and drag rightwards to fill the formula to cell G2.
- And now you can input the name of a sales representative that you need to view into cell E2.
You can see that the corresponding sales volume will immediately change in cell F2 and G2 when the name in E2 changes.
By using the formula, you can view values more convenient. Besides, except for the MATCH and INDEX function combination, you can also use the VLOOKUP function. And you need to input this formula into cell F2:
In cell G2, change the argument “2” into “3”:
When you change the name in cell E2, the numbers will also change in these two cells. Both of the two different formulas are very helpful. You can choose to use them according to your actual need.
Method 2: Use VBA Macros
Except for using Excel functions, you can also use the VBA macros to finish this task quickly.
- Press the shortcut keys “Alt +F11” on the keyboard to open the Visual Basic editor.
- And then in the editor, double click the sheet that contains the range in the VBA Project area. Here we will double click the “Sheet1” in this example.
- After that, input the following codes into the code window.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$E$2" Then For i = 2 To 31 If Range("A" & i) = Target Then Range("F2") = Range("B" & i) Range("G2") = Range("C" & i) End If Next i End If End Sub
We will first judge whether the target cell is the cell E2. And then when the content in E2 changes, the values in other two cells will also change accordingly. In your actual worksheet, change the necessary elements of the cell reference and the source range.
- Now come back to the worksheet. Actually you have finished the setting.
Whenever you change the name in the cell E2, the numbers in other two cells will change automatically. You don’t need to run the macro manually. And actually the effect is the same as the result of using formula.
A Comparison between the Two Methods
You can see that both the two methods are very useful. However, you will certainly only need to use one method. And the table below will demonstrate the advantages and disadvantages of the two methods.
Use VBA Macros
|1. There are two different types of formulas available. You can choose one type according to your need.
2. The numbers in the other cells will change immediately when you change the value in the first.
|1. You don’t need to manually run the macros. It will take effect automatically.
2. Using VBA codes will not mess up the original worksheet.
|1. If you need to use the result in other range or other worksheet, you need to paste as values.
2. If you are not familiar with the formula combination, you will sometimes meet with troubles when changing arguments in formula.
|1. When you change a value in any of a cell in this worksheet, the Macro will judge the cell. This may lead to the low performance.
2. If you are not familiar with VBA, you will probably meet with errors when modify the codes.
Thus, the next time if you need to view values, you will know which method is right for you. As for the result, both of the two methods can satisfy you.
A Recovery Tool is the Most Necessary
If you need to use Excel to finish your tasks, you need to prepare a recovery tool. This is because there are many reasons that will cause damage to your mission-critical files. And some of the factors are unavoidable, such as the human error. With this recovery tool at hand, you are able to repair Excel xls file corruption easily. Thus, you will never need to worry about your data and information in Excel files.
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word document problem and outlook repair software products. For more information visit www.datanumen.com