'tricking' excel to recognise a table header outside of the table

    Good afternoon,

    I have a table and am using the =filter formula to create lists from the table. My question - is it possible to trick the filter syntax to see a #$#$ reference (in another cell) as the header reference?

    I.e. instead of

    =FILTER(Table5[[#Data],[EOS300]],(Table5[[#Data],[EOS300]]<>"")*(Table5[[#Data],[Setting]]=T$1),"")

    I want to substitute the [EOS300]] reference with text in, say, $A$5.

    Note - the text in $A$5 will contain a valid name - I just want to be able to drag the formula.

    Thanks.

    Paul

    A Table's Header Row cannot contain formulas.


    What I do is set up the Table as normal. I then add a row with the formulas in above the existing Header Row and format it to look the same as the Header Row. Finally I hide the proper Header Row

    Hi Roy,

    Thank you for your reply.

    I have, perhaps not been clear. Attached is an example.

    I have filtered lists based on the table data. If we take the EOS300 filter list (column H). At the moment the 'include' part of the filer array refers to


    ,ShutterSpeeds[[#Data],[EOS300]]<>"",


    (I have included <> to remove blanks).


    What I am trying to do is replace [EOS300]] with the value in H1.


    I expect it is not possible but it would be helpful if I could.

    Thanks.

    Paul

    Sadly ... your explanation of your objective is quite confusing ...

    and you are presuming, right from the beginning, that the solution you need is to 'trick' Excel ...


    Could you clearly explain :


    1. your starting point with the data available

    and

    2. your final expected result ... even if you draft it manually ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    The method I first described is the only way that I know of to do this.


    It's not idea, you cannot see the AutoFilter arrows, but it does trick Excel.

    You could use INDIRECT:


    =FILTER(INDIRECT("ShutterSpeeds["&H1&"]"),INDIRECT("ShutterSpeeds["&H1&"]")<>"","")


    or a non-volatile version:


    =FILTER(INDEX(ShutterSpeeds,,MATCH(O1#,ShutterSpeeds[#Headers],0)),INDEX(ShutterSpeeds,,MATCH(O1#,ShutterSpeeds[#Headers],0))<>"","")


    If your version of Office has LET available, you could simplify those a little.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login