A lot of Mac users knock Excel for not being as pretty as Numbers on the Mac, but at the 2. Select the cell range you wish to use and click OK. In a couple of seconds you will see a separate Check back in the next couple of weeks because we will be covering some of the analytical tools in detail. In Excel 2013, the IF function serves to compare cells regardless of their position. In many cases, however, you don't need to go through the hassle of writing a formula just to check how cells compare. To search for duplicates, to find cells above an average value or to style cells based on their values.
In this article, we will discuss an important topic which is how “Excel reference cell in another sheet dynamically”.
Here we will refer a spreadsheet cell in another spreadsheet. We will also do the referencing in other workbooks.
The referencing will be done by using the INDIRECT function. So, we need to know about this function first.
Table of Contents
- Let’s introduce you to Excel INDIRECT function
Download The Working File
Let’s introduce you to Excel INDIRECT function
The Indirect function in Excel returns an authentic reference from an assigned text string. It is categorized as the reference function in Excel.
Syntax
=INDIRECT (Text_ref, [Cell]) which returns an authentic worksheet reference.
Arguments
Text_ref– supplied text reference.
Cell– It is an optional argument. You can indicate it directly like, A1, B1 etc. or in the form of R1C1. It is used as Boolean input and by default, A1 is taken as True.
Notes
- For referring to another worksheet the worksheet must be open.
- The INDIRECT function evaluates the result in real time
- The cell is taken as Boolean input and it is an optional argument. If it is taken as True then the cell reference style will be like this =A1
- If Cell is taken as False, then the worksheet will create a reference style of R1C1.
Some examples of INDIRECT Function (Get clear idea)
Let us look some of the examples in the picture below. In the image below, column E is holding the formulas from column D.
How to Reference a Cell in Another Excel Worksheet Dynamically
Let`s create a workbook named Book1. In this workbook create 3 different worksheets named as Sheet1, Sheet2, Sheet3. In Sheet1 type 1-10 serially in the range A1:A10. In Sheet3 type 10-19 in the range A1:A10. We will do the worksheet referencing in Sheet2. So initially nothing is inserted in this sheet. After you provide the input in Sheet1 and Sheet3, do the followings in Sheet2 to see the magic.
In cell A2 we write Sheet1 and cell B2 contains the cell name A9. In cell C2 we will get the reference result. The formula is written in cell C2 which is
=INDIRECT('' & A2 & '!' & B2)
For the clarification, it is written in cell D2 by typing =FORMULATEXT(C2). The formula will output number 9 which is written in cell A9 of Sheet1.
The INDIRECT function first gets the reference from A2, where Sheet1 is written. Just before indicating A2 we used a set of double quotes. These quotes indicate the text string. As Sheet1 is written in cell A2 so, the function will take Sheet1 as the reference. “&” is used to combine the arguments together. “!” sign is used to separate sheet from a cell. So, by using “!” with “” we are actually referring Sheet1. Here a single quote is placed in between the double quotes for preventing errors (when the worksheet name has space in it). At the end of the Formula B2 is written which indicates the address of the cell.
In the same way,
=INDIRECT('' & A7 & '!' & B5)
which is written in cell C8 will return number 15 because we are referring to A7 which contains the name Sheet3 and in B5, A6 is written.
Note: If any of the cells contain no values. The formula gives an error result. To prevent this, you can use the below formula:=IF(OR(A5=',B5='),',INDIRECT('' & A5 & '!' & B5))
Referencing to Separate Workbook Directly
Create a different workbook named Book2 and in there create a spreadsheet named Spreadsheet1. In Spreadsheet1 from A1 to A11 write down 20 to 30. Create a new spreadsheet name Sheet4 in Book1.
To insert a data in Sheet4 from Spreadsheet1 of Book2 you can do the things below.
- Put an equal sign on cell A6 of Sheet4.
- Then go back to Spreadsheet1 of Book2 and select A6 where 25 is written and press enter. You will see the below result in Sheet4.
- As you can see in cell A6 of Sheet4 =[Book2.xlsx]Spreadsheet1!$A$6 is written. This happened because we did the referencing of Spreadsheet1 of Book2 in Sheet4 of Book1.
How you can reference a separate workbook dynamically
The previous process was for direct referencing. For the dynamic referencing follow the below processes.
![Cell Cell](/uploads/1/2/5/8/125876452/720178019.gif)
- In A3, A4 and A5 of Sheet4 write Book2.xlsx, Spreadsheet1, and A6.
- In cell A7 write
=INDIRECT('[' & A3 & ']' & A4 & '!' & A5)
and press enter.
- You will see the value 25 as the A6 cell of Spreadsheet1 contains the value 25.
Here, the formula uses square brackets which are used for structured referencing. Here we refer the workbook with the square brackets. After that, the referencing of worksheet and cell is the same as before.
Note: Keep that in mind, you need to type the workbook name with its extension (.xlsx, .xlsm, or so) if your windows system is showing the file names with their extensions. Here we type the workbook name Book2 like this Book2.xlsx because, on my Windows PC, my files are shown with their extensions.
Related Readings
Conclusion
It may look like that dynamic referencing of a spreadsheet is not that important. But, when you have a lot of worksheets and you need to gather the specific information of those sheets in a single worksheet you have to carry out the dynamic referencing for doing it easily.
Hope you find this article useful. Give us your feedback and stay tuned.
You will agree that an Excel sheet can run up to thousands of rows and columns. You will also agree that when you are working on one such worksheet, you might have to navigate into deep rows and columns. This takes you away from the reference row and column i.e cell A1, where you might need to revisit frequently.
Now, using the scrolls isn’t one of the best ways to get back to that cell. Rather, we will see three better and cool ways to do this.
So, without further ado, let us jump to A1.
Cool Tip: Also check out our post on various ways to jump from one sheet to another. There are a lot more than what you use.
1. Using Keyboard Shortcuts
To me this is the easiest and the quickest way to get back to A1 from anywhere in the worksheet. All you need to do is press these two keys together: Ctrl + Home
Exception: This method will not take you to A1 if you have freeze panes activated on rows and columns. In such a scenario it will take you to the first row, first column after the freeze panes.Take a look at the image below. Here, rows 1 through 4 and columns A and B are in freeze mode. So pressing Ctrl + Home will take you to C5.
2. Using Go To Dialog Box
This method has an extra step and takes you a bit longer to get to A1. However, unlike method 1, it assures your jump to A1 no matter where you are and no matter what’s in freeze mode.
Step 1: Press either F5 or the combination Ctrl + G. That will launch the Go To dialog box.
Step 2: On the dialog box, under Reference text box, type A1 and hit Enter or click on the OK button.
Quick Tip: This method can be used to navigate to any cell quickly. All you need to know is the reference of the cell you want to jump to.3. Using Name Box
We know that MS Excel has a name box that displays the reference of the cell you are in. You can actually use this box to go to cell A1.
To do that, type A1 in the name box and hit Enter. Immediately, the reference will shift to A1.
There is a second trick here. You can actually define a name for cell A1, say, for example, Home. Then you can click on the name box drop down and select Home to jump to A1.
Conclusion
How often do you navigate away from the A1 reference? And how often are you required to get back? If the answer is, “very often”, you need to remember these tricks. Not all of them though, you can pick the ones you like the most or are comfortable with most.
Know of any other similar trick? Do share.
Also See#Lists #msexcel Did You Know
Windows 10 is the last Windows since Microsoft is changing the approach of building and delivering OS.