Forum Discussion

Fabs1's avatar
Fabs1
Copper Contributor
Dec 09, 2025

pulling changing number of rows into another sheet, where you have categories in rows

i have a template document for which data can be populated. categories of information are populated both in columns and rows, meaning within a column you can also have categorisation of data. I want to be able to pull data from sheet1 into sheet2 were the rows in sheet2 will adapt depending on sheet1 and no space will be shown between the rows categories.

excel

1 Reply

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Dec 11, 2025
    =IFERROR(INDEX(A$2:A$32,SMALL(IF($C$2:$C$32="Shenzhen",ROW($C$2:$C$32)-1),ROW(A1))),"")

    Is this similar to what you are looking for? The formula is in cell I2 and dragged across range I2:N10 and works in all versions of Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024. The formula works across two sheets as well. For illustration i've made an example within one sheet. With recent versions of Excel you can use the FILTER function to simplify the solution.

     

Resources