Indirect within Filter Formula

[SeliM]

I have a register for drivers that holds 20 columns of information, 3 of which cover cancellations if activated.

I have a need for a simple register of current drivers, using "=CHOOSECOLS(FILTER(INDIRECT(Lookup!$O$2),Register!R6:R8=""),1,3,4,8,9,11) I can get a current list.

You will note an indirect reference which is "Register!$A$6:$T$8", I would like to use indirect again instead of "Register!R6:R8 to make it dynamic. The change to Indirect (Lookup!$O$3), (the range that indicates cancellations) results in error #Value.

(Using the indirect range eliminates all the zeros that would result using register's full range.)

Would you have any advice on how I can redress this problem please?

Mel

 

[6StringJazzer]

I do not have a sheet set up try your same formula, but can you show the formula that gives the #VALUE error? It seems like that should work.

 

[Fluff]

Make sure that the range listed in O3 is one column wide & the same number of rows as listed in O2