Using Filter Formula with Named Ranges across Sheets

0
votes

I have a dynamic named range that I use and I am looking into using the filter function to reduce that data down on another worksheet using references to criteria on that new worksheet.

=FILTER(Loss_Run, ULGTACCT Code = $B$2,"")

ULGTACCT is the column name,

$B$2 will have a number stored as text

Loss_Run is dynamic named range that is an indirect formula (This could be the cause of my headache)

Online I found If its named I can just reference the name of the range and columns by name but since its a formula will that not work?

Update =FILTER('Loss Template'!A:M, 'Loss Template'!A:A = $B$2,"")

The formula directly above references a "printout" of the named range that is used in workbook and if I hardcode the value for B2 it works but it does not work if i try to reference B2.

Note: It would be kinda difficult to mock up Loss_Run because of the nature of the range being a complicated indirect().

1
excelexcel-formula
please post some links to photos so we can see what you are doing. Also the exact formula you are trying. It is very hard to find errors without all the information. If you need to, mock up the data. - Scott Craner
@ScottCraner is that better? - PotterFan
is B2 a number or text? VALUE($B$2) will return an error if it is text that is not possible to convert to a number. - Scott Craner
it is a formula that returns a number - PotterFan
Then there is the possibility that your data to which you are trying to match are numbers stored as text. - Scott Craner

1 Answers

0
votes

I had to apply the text function to the criteria range.

    =FILTER('Loss Template'!A:M, 'Loss Template'!A:A=TEXT($B$2,"0"),"Still Not 
    Working")

The issue was the target range was numbers as text and just formatting the criteria cell as text was not enough.