possible to choose table with formula?

[Pofski]

Hi everybody,


just a quick question. Does anybody know of a way that you could integrate a sort of selection of table in a formula?


For example, you have a cell that goes from 1 to 5

and in a formula

(for example =SUMIFS(Table1[ActualOpen];Table1[Request Service Date];">="&BC22;Table1[Request Service Date];"<"&BD22;Table1[Schd Serv GEMTS Plant Code];$BP$6))


the table switches from table1 all the way to table5 depending on what's in the first cell.


Thanks in advance


Sincerely

 

[Hui]

You could use an Indirect and a Choose function like

=SUMIFS(Indirect(Choose(Ref_Cell,Table1[ActualOpen],Table2[ActualOpen],Table3[ActualOpen],Table4[ActualOpen],Table5[ActualOpen];....


Where the Ref_Cell has values 1 to 5

 

[NARAYANK991]

Hi Pofski ,


Another method is :


=SUMIF(INDIRECT("Table"&Ref_Cell&"[ActualOpen]");Criterion)


where Ref_Cell takes on the values 1 through 5.


For the formula you have given , this would now be :


=SUMIFS(INDIRECT("Table"&Ref_Cell&"[ActualOpen]");INDIRECT("Table"&Ref_Cell&"[Request Service Date]");">="&BC22;INDIRECT("Table"&Ref_Cell&"[Request Service Date]");"<"&BD22;INDIRECT("Table"&Ref_Cell&"[Schd Serv GEMTS Plant Code]");$BP$6))


Narayan

 

[jeffreyweir]

Hui, I'd go the whole hog and ditch the INDIRECT altogether...especially given that we're doing a calculation-intensive SUMIFS, and especially if this concerns the same data as Pofski's question at http://chandoo.org/forums/topic/multicheck-across-multiple-tabs that involved millions of rows.


So wherever Table1[SomeColumn] appears in this:

=SUMIFS(Table1[ActualOpen];Table1[Request Service Date];">="&BC22;Table1[Request Service Date];"<"&BD22;Table1[Schd Serv GEMTS Plant Code];$BP$6)


replace it with this:

CHOOSE($A$1,Table1[SomeColumn],Table2[SomeColumn],Table3[SomeColumn],Table4[SomeColumn],Table5[SomeColumn]).

...where $A$1 is the cell that goes from 1 to 5.


It will make for a very long formula, but a very quick one compared to INDIRECT. Should look something like this:

=SUMIFS(CHOOSE($A$1,Table1[ActualOpen],Table2[ActualOpen],Table3[ActualOpen],Table4[ActualOpen],Table5[ActualOpen]);CHOOSE($A$1,Table1[Request Service Date],Table2[Request Service Date],Table3[Request Service Date],Table4[Request Service Date],Table5[Request Service Date]);">="&BC22;CHOOSE($A$1,Table1[Request Service Date],Table2[Request Service Date],Table3[Request Service Date],Table4[Request Service Date],Table5[Request Service Date]);"<"&BD22;CHOOSE($A$1,Table1[Schd Serv GEMTS Plant Code],Table2[Schd Serv GEMTS Plant Code],Table3[Schd Serv GEMTS Plant Code],Table4[Schd Serv GEMTS Plant Code],Table5[Schd Serv GEMTS Plant Code]);$BP$6)