With the ampersand (&) included as the concatenation symbol, Excel evaluates this formula as “ SUM(D6:D9)”, which is exactly what we want.įrom the right section, you can see that by changing values in Cells D2 and D3, the formula in D15 is evaluated by Excel to be “ SUM(D8:D10)” which is also what want. Look at cell D15 in the above figure, the formula is “=SUM(INDIRECT(D$14&$D$2&”:”&D$14&$D$3))”.Įvery cell reference within the INDIRECT part of this formula is evaluated to equal the contents of the cell.ĭ$14 is evaluated as D, $D$2 is evaluated as 6, and $D$3 is evaluated as 9. Luckily, we have another approach with the INDIRECT function. Obviously changing the formula is time-consuming if there are many regions. You can see that we need to modify the formula every time we want to see total sales of different products in one region. Usually, we will use the formula “ =SUM(D6:D9)” to compute the total sale of Product 1 through Product 4 in East and “ =Sum(D8:D10)” to compute the total sale of Product 1 through Product 4 in East. How can you use the INDIRECT function to calculate the total sales of any combination of consecutively numbered products, such as Product 1 through Product 4, Product 3 through Product 5, and so on? Suppose that we have data for the sales of 7 products in four regions (East, West, North, and South). Case 1: Change cell or range reference without changing the formula
#Practice sheets for excel how to#
With the above knowledge, I will give you examples showing how to solve problems in real life. You can compare this formula together with the formula in cell D2 to have a better understanding. The INDIRECT does not evaluate reference “B2”. In cell D6, entering the formula “=INDIRECT (ADDRESS (2,1))”, I get “B2” which is the content of cell A2.
Let’s take ADDRESS (2,1) as an example, Excel ADDRESS function will return “$A$2”. Then is it possible to use the INDIRECT function together with the ADDRESS function? What will you get? Obviously, “New York” and “6” are not valid references.Īs for the reference, we all know that the ADDRESS function can yield the actual cell address associated with a row and column. Please note that if the reference is not valid, Excel will return “#REF!” error, just like what we get for cells D4 and D5. You can see that the content of cell A3 is “B4”, but the returned value is “2” instead of “B4” when I enter formula “=INDIRECT(A3)” in cell D3.īecause “B4” is a reference and Excel evaluated this reference and returns the content in cell B4 (see cells with blue borders). In order to give you a good explanation, I also put formulas as a text string for all cells (Cell D2, D3, D4 & D5) in column E. I put formulas “=INDIRECT(A2) in cell D2, then copy and paste this formula down up to column D. The left section shows sample data and the right section applied the INDIRECT function to retrieve data from the left section. The below example can give you a better understanding of it. With the INDIRECT function, you can change the reference to a cell within a formula without changing the formula itself. In essence, it returns the references specified by a text string and the returned references can be immediately evaluated to display their content. Knowing how to use it will enable you to solve many seemingly unsolvable problems, though mastering it is a little bit difficult for a newbie. Cell B1Įxcel’s INDIRECT function: Syntax Now it’s time for us to discuss the INDIRECT function. It will help you better grasp relative reference and absolute reference. You can make a complicated formula and copy it elsewhere to see what will happen. Here is a summary of what you will get if we change the above example a little bit. “$A1” implies that column reference does not change while “$A$1” tells that both the column reference and row reference do not change when copied. “A$1” means the row reference does not change when copied. Read More: Financial Planning with Excel Solver The dollar sign ($) can precede the column reference, the row reference, or both. It is useful and allows a formula like SUM to be copied across or down the page and automatically refer to the new column or row.īut in some situations, you want some or all of the references to remain fixed when they are copied elsewhere and this is where the dollar sign ($) can be used. And it will be “=B2” for cell C2.ĭid you notice that both the column reference and row reference can change? If you paste it into cell C1, the formula will be “=B1”. This formula will change as “=A2” when you copy and paste it into cell B2. Suppose you have a simple formula “=A1” in cell B1.
#Practice sheets for excel download#
Download working files Get an Idea How Absolute and Relative Reference Work