refer to other cell with reference cell in Excel
Q65. I want to pull data from a table using a reference cell in table. Is there a function to support that?
For example, I maintain financial report for the year. To make it simple, I have set up a month selection field (cell F2), that would be used to show table data for that month only.
I have defined Monthend Date in a table. For the month selected, can I get Montheend Date without relying on VLOOKUP or Index / Match?
To do it in Excel, here is the answer:
a) Enter the formula =OFFSET(B2,F2,1) in cell F3 where Montheend Date for the Month selected is required.
The first argument "B2" is the reference cell in the Table (typically top left cell of the Table).
The second argument "F2" refers to the cell containing the row offset from reference cell. In this case, row offset is determined by user's choice of Month.
The 3rd argument "1' refers to the column offset from reference cell.