Filter Function

[asif187]

Hi,

The green row number is of a table that is attached to a query that appends multiple table. Every time I refresh my data the row number in green changes. The red one, however, does not change.

For the filter function to work I have to manually change the row numbers in the red one to match the rows in green.

Is there anyway where the red texts adjusts itself/Change to the green one dynamically ?

Can we extract the green value (using FORMULATEXT and LEFT function) to a cell and then link that cell in place of red ? or is there any other way ?


=IFERROR(IF(BS7="ALL",FILTER(ALL_ENTRY!$B$3:$AE$2625,(ALL_ENTRY!$E$3:$E$2625=LABOUR!BS6)*(ALL_ENTRY!$F$3:$F$2625>=LABOUR!BS4)*(ALL_ENTRY!$F$3:$F$2625<=LABOUR!BS5)),
FILTER(ALL_ENTRY!$B$3:$AE$2625,(ALL_ENTRY!$D$3:$D$2625=LABOUR!BS7)*(ALL_ENTRY!$E$3:$E$2625=LABOUR!BS6)*(ALL_ENTRY!$F$3:$F$2625>=LABOUR!BS4)*(ALL_ENTRY!$F$3:$F$2625<=LABOUR!BS5))),
"No Entry")

 

[GraH]

Why don't you refer to the table columns instead of a range?