Forum Discussion
Move data up if blank or move to next row and retrieve requested data
Hi, in the attached sample I have 4 sheets. The main data sheet and three bloodline sheets. Formulas have been added (VLOOKUP, IFERROR, IF) to separate the date on their respective sheets. However, what I want to do is have the data move up if the row above is blank.
or
have a formula check if the correct bloodline is present, if not, move onto the next row and retrieve data that matches the request.
Office Version: 2021
Thanks in advance.
I FIRST suggest you use a PivotTable instead and then you add the corresponding filter.
Alternatively (if you really don't want a Pivot Table for some reason), still don't use VLOOKUP etc... and just use FILTER() instead (which according to my MS ref, Excel 2021 should have FILTER and dynamic arrays) so something like:
=INDEX(FILTER(DataTable, DataTable[Bloodline]="Han"), ,{1,9,10})and if you make $C$1 the filter 'name' so the Header row would be like:
Name Gender Han
then the formula could be:
=INDEX(FILTER(DataTable, DataTable[Bloodline]=$C$1), ,{1,9,10})
and quite honestly why even have that last column since it will all be the same?
3 Replies
I FIRST suggest you use a PivotTable instead and then you add the corresponding filter.
Alternatively (if you really don't want a Pivot Table for some reason), still don't use VLOOKUP etc... and just use FILTER() instead (which according to my MS ref, Excel 2021 should have FILTER and dynamic arrays) so something like:
=INDEX(FILTER(DataTable, DataTable[Bloodline]="Han"), ,{1,9,10})and if you make $C$1 the filter 'name' so the Header row would be like:
Name Gender Han
then the formula could be:
=INDEX(FILTER(DataTable, DataTable[Bloodline]=$C$1), ,{1,9,10})
and quite honestly why even have that last column since it will all be the same?
