![]() ![]() Enter the following Self Referencing If formulas as shown in the illustration below: The formula in cell D26 returns year 1 sales if scenario 1 is active or itself if any other scenario is active. Whilst this is a very simple example of its use, the applications (and benefits) are considerable where more complicated formulae are required. Build the model shown in the first illustration or download it. To ensure that the same formula is applied all the way down, I used an R1C1 reference within an INDIRECT function: =SUM(INDIRECT("C",FALSE)+$A$2) The problem is that when I go down to cell B3, the formula is different: =SUM(A3+$A$2) Using A1-style referencing, I could type the following into cell B2: =SUM(A2+$A$2) The “Total” column shows the value in the “Number” column added to the value in cell A2 (9). In the following table, there are two columns: Number Simply include an INDIRECT function within your equation where a1=FALSE and the R1C1 reference can be seamlessly included in a sheet with A1-style cell referencing. The INDIRECT function only has two parameters Reference Text ( Ref_text) and an a1 True/False statement to indicate whether the formula is A1-Style (True) or R1C1 style (False). The INDIRECT function works by converting text into a cell reference. Thankfully there’s a way to include R1C1 references into an A1-style sheet using the INDIRECT function. Whilst Excel will permit a user to switch between A1 and R1C1 referencing overall, that’s not always desirable. ![]() An illustrative example of how relative R1C1 cell referencing works.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |