Creating a dynamic formula so it takes a number within a cell as the row number-CodePudding

I have a master sheet, that holds all data in a matrix form (Criteria along the top i.e. Level 0,1,2,3,4,5) and criteria along the side (e.g. Organisation, Governance, Finance, Strategy). Within the master sheet, there is data that matches these criteria, and therefore plotted in the matrix. However, there is not data for all criteria, and therefore, there are blanks.

I would like to use this master sheet as the data source, and use the same format, but i would like to divide each section (I.e. Organisation, Governance, Finance, Strategy) into its own sheet and display the data for that criteria only.

I will also want to make this dynamic to ensure that if the criteria was changed in the future, the sheets would still work.

Therefore, I am using the following formula

=(UNIQUE(FILTER('MasterSheetGrid'!D8:I8,'MasterSheetGrid'!D8:I8<>"")))

This formula works well, however the row numbers are not dynamic and the formula may break in future. Therefore, i am wanting to use the following formula

=MATCH($C6,'MasterSheetGrid'!$C:$C,0)

to search for the row number, and then use this formula or output as the numerical row number in the formula above, but i receive an error each time.

>=(UNIQUE(FILTER('MasterSheetGrid'!D" &(MATCH($C8,'MasterSheetGrid'!$C:$C,0)& ":I" &(MATCH($C8,'MasterSheetGrid'!$C:$C,0)& ",'MasterSheetGrid'!D" &(MATCH($C8,'MasterSheetGrid'!$C:$C,0)& ":I" &(MATCH($C8,'MasterSheetGrid'!$C:$C,0)& "<>"")))

Can anyone help?

CodePudding user response：

You can use:

INDEX('MasterSheetGrid'!D:I,MATCH($C6,'MasterSheetGrid'!$C:$C,0),0)

to get the desired row. So then something like:

=LET(DataRange,INDEX('MasterSheetGrid'!D:I,MATCH($C6,'MasterSheetGrid'!$C:$C,0),0),UNIQUE(FILTER(DataRange,dataRange<>""),TRUE))