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?

excel refer to other cell with reference cell

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.

excel excel refer to other cell with reference cell

 

You can find similar Excel Questions and Answer hereunder

1) Can I add a small chart to Table data to make it visually appealing and easy to interpret?

2) How can I clear cell after activating a routine when there is a change in value of a cell?

3) How do I know which cells on the worksheet contain Conditional Formatting?

4) How can I quickly navigate to a frequently used field in a voluminous worbook?

5) How can I identify all cells with Data Validation in my WorkSheet?

6) Remove the apostrophe cell text values in Excel

7) Highlight row of selected cell in Excel

8) How can I avoid updates to cell values during macro execution?

9) How can I find the last used cell in a Column in VBA?

10) How can I identify the cells that are dependent on a particular cell?

11) Vlookup to return multiple values in same cell in Excel

12) How can I ensure that user enters only certain acceptable values in an input cell?

13) How do I enter a formula in a cell using VBA (using Absolute Reference)?

14) How can I identify the cells that influence a particular cell to help with debugging of my spreadSheet or understand a spreadSheet that I inherited?

15) I want to add a trend indicator symbol next to my sales data - how can I do that?