One way to automatically move a row when the date in a cell arrives is to use a VLOOKUP function with an INDIRECT function. The VLOOKUP function can look up a value in a table and return a corresponding value from another column. The INDIRECT function can create a reference to a range based on a text string. By combining these two functions, you can dynamically refer to different sheets based on the date in a cell.
Procedures
Here are the steps to create a formula that can automatically move a row when the date in a cell arrives:
- Create two sheets, one named “current” and the other named “future”. In each sheet, enter the data you want to move, such as customer accounts, dates, and other details. Make sure the dates are in the same column in both sheets, and that the column headers are the same.
- In the “current” sheet, select a cell where you want to display the data from the “future” sheet. Enter the following formula:
=VLOOKUP(B$1,INDIRECT($A2&"!A:B"),2,FALSE). This formula assumes that the date you want to look up is in cell B1, the sheet name is in cell A2, and the data range is in columns A and B. You can adjust these references as needed. - Copy the formula across the row and down the column to fill the cells with the data from the “future” sheet. The formula will automatically update the references based on the date in cell B1 and the sheet name in column A.
- To move the row from the “future” sheet to the “current” sheet, you can either change the date in cell B1 to match the date in the “future” sheet, or change the sheet name in column A to “current”. The formula will then return the data from the “current” sheet instead of the “future” sheet.
Comprehensive explanation
The VLOOKUP function has four arguments: lookup_value, table_array, col_index_num, and range_lookup. The lookup_value is the value you want to find in the first column of the table_array. The table_array is the range of cells that contains the data you want to return. The col_index_num is the number of the column in the table_array that contains the value you want to return. The range_lookup is a logical value that specifies whether you want an exact match (FALSE) or an approximate match (TRUE) for the lookup_value.
The INDIRECT function has one argument: ref_text. The ref_text is a text string that represents a cell or a range of cells. The INDIRECT function returns a reference to the cell or range that the ref_text describes.
In this case, the lookup_value is the date in cell B1, which is the date you want to move the row based on. The table_array is created by the INDIRECT function, which concatenates the sheet name in cell A2 with the text string “!A:B”. This creates a reference to the range A:B in the sheet specified by cell A2. The col_index_num is 2, which means you want to return the value from the second column of the table_array. The range_lookup is FALSE, which means you want an exact match for the date in cell B1.
The formula works by looking up the date in cell B1 in the first column of the range A:B in the sheet specified by cell A2. If it finds a match, it returns the value from the second column of the same row. If it does not find a match, it returns an error value. By copying the formula across the row and down the column, you can return the values from the other columns and rows of the table_array.
To move the row from the “future” sheet to the “current” sheet, you can either change the date in cell B1 to match the date in the “future” sheet, or change the sheet name in column A to “current”. This will change the table_array reference to the “current” sheet, and the formula will return the values from the “current” sheet instead of the “future” sheet.
Scenario and example
Suppose you have the following data in the “future” sheet:
| Customer ID | Date | Amount | Status |
|---|---|---|---|
| 1001 | 01/02/2024 | 500 | Active |
| 1002 | 01/03/2024 | 600 | Active |
| 1003 | 01/04/2024 | 700 | Active |
| 1004 | 01/05/2024 | 800 | Active |
| 1005 | 01/06/2024 | 900 | Active |
You want to move the row of data for customer 1003 to the “current” sheet when the date in cell B1 is 01/04/2024. You enter the following formula in cell B2 of the “current” sheet: =VLOOKUP(B$1,INDIRECT($A2&"!A:B"),2,FALSE). You copy the formula across the row and down the column to fill the cells with the data from the “future” sheet. You get the following result:
| Customer ID | Date | Amount | Status |
|---|---|---|---|
| 1001 | #N/A | #N/A | #N/A |
| 1002 | #N/A | #N/A | #N/A |
| 1003 | 01/04/2024 | 700 | Active |
| 1004 | #N/A | #N/A | #N/A |
| 1005 | #N/A | #N/A | #N/A |
The formula returns the data for customer 1003, because the date in cell B1 matches the date in the “future” sheet. The other rows show error values, because the date in cell B1 does not match the dates in the “future” sheet.
To move the row from the “future” sheet to the “current” sheet, you can either change the date in cell B1 to 01/05/2024, or change the sheet name in cell A3 to “current”. The formula will then return the data from the “current” sheet instead of the “future” sheet. You get the following result:
| Customer ID | Date | Amount | Status |
|---|---|---|---|
| 1001 | #N/A | #N/A | #N/A |
| 1002 | #N/A | #N/A | #N/A |
| 1003 | 01/04/2024 | 700 | Active |
| 1004 | 01/05/2024 | 800 | Active |
| 1005 | #N/A | #N/A | #N/A |
The formula returns the data for customer 1003 and 1004, because the date in cell B1 matches the date in the “current” sheet or the sheet name in column A matches the sheet name “current”. The other rows show error values, because the date in cell B1 does not match the dates in the “current” sheet or the sheet name in column A does not match the sheet name “current”.
