Assume there is data in range A2:BG2185 of a worksheet (Range A1:O1 have headings). In column A are years and in column B are Countries. Other columns have some text/numeric data. In column A are years ranging from 1984 to 2009 for each country mentioned in column B. Therefore, Albania would appear in range B2:B27 and 1984-2009 would appear in range A2:A27. Next, Algeria would appear in range B28:C53 and 1984-2010 would appear in range A28:A53 and so on. For 84 countries, the number of rows occupied will be 2,184. You may download the workbook from here.
The objective is to show in another worksheet of the same workbook, all data from year 1990 onwards (year 1990 included) for each country. Furthermore, if data gets added (by rows) or edited in this base data sheet, the result sheet should update.
Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem
Solution for MS Excel 2010 and higher versions
If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem. The result will be dynamic and refreshable (just as in a Pivot Table).
You may refer to my solution at this link
Solution for all versions of MS Excel
The following process should get the desired result
1. Select range A1:O82 and press Ctrl+F3 > New > Name. In the Name box, enter Dummy and click on OK.
2. Select range A1:O82 and press Ctrl+T. Ensure that the “My data has headers” box is checked and click on OK
3. Save the file (assume on the Desktop for now)
4. Open a blank worksheet and go to Data > From Other Sources > From Microsoft Query
5. Select Excel files and click on OK
6. In the right had side panel, navigate to the folder where you saved the file in step 3 (Desktop in this case). In the left hand side panel, select the specific Excel file which you saved in step 3 and click on Next
7. With Dummy appearing in the left hand side panel, click on the > symbol to transfer all columns of Dummy to the right hand side panel and click on Next
8. In the column to Filter box, select year and in the right hand side Year box, select Greater than Equal to and then select 1990
9. Click on OK Twice
10. Select “Return Data to Microsoft Excel” and click on Finish
11. In the Import Data box, click on OK
The data on this sheet will now show data for all countries from year 1990 onwards (year 1990 included) and this will be linked to the source data sheet. If you add data by rows to the source data sheet or edit data in existing rows, just right click on any cell in the result sheet and select Refresh.