Dec 07 2021 08:58 PM
I googled around, but couldn't find an answer to how to do this (but, at the same time, I can't believe that this hasn't been requested enough that Microsoft hasn't put it into Excel, yet).
I make a lot of tables where the main area of the table is calculated from the values of the row/column headers (for example, imagine row headers being the date and column headers being the hour of the day, and the main table would be, say, the position of the sun at that day/time). Because of this, I would make my top-left calculated cell (B2) to refer to $A2 for the row header and B$1 for the column header. Then, I can just drag-fill the rest of the table. However, for complicated formulae, managing these mixed references is time-consuming and error-prone.
What I'd really love is the ability to mark a row of cells to default to row-absolute referencing and a column of cells to default to column-absolute referencing. That way, when I merely click on something from the headers, the cell reference already would have the desired reference type. Is there a way to do this in Excel?
Dec 08 2021 12:28 AM
Dec 08 2021 12:39 AM
@jemenake You can also use dynamic arrays, like shown in the attached Excel file, which contains no more than 3 formulas!