How can we help?

New post
1

Dynamic indexing for spreadsheets

Avatar
Frederic Thouin
Answered
Follow
Would be nice to be able to select a row/column of a spreadsheet based on another variable or a parameter instead of having to select it explicitily in the formula.
Date Votes

5 comments

  • 0
    Avatar
    T V

    I second this! I have a pulldown menu, that controls several metrics, one of which is a bar graph. The bar graph pulls data from a spreadsheet - I would like the data to change based on the value of the variable as set in the pulldown. For example, choosing "alpha" from the pulldown displays data from column C:C; "beta" displays column D:D, and so forth.

    If there is another way to do this, please let me know. I have tried lookup() and switch() to no avail.

     

     

  • 1
    Avatar
    Zach Kathnelson

    Hi Guys - sounds like you should be able to accomplish what you're looking for with the SWITCH function.  SWITCH let's you execute a completely different formula based on a variable value (ie., based on what a user has selected in a dropdown).  So, Trudy, if a User chooses Alpha, you can show column C:C.  If they choose Beta, you can show column D:D.  

    SWITCH ($Greek Letter, "Alpha", C:C, "Beta", D:D).  This would work even if C:C and D:D are from completely different Data Sources.  

    Let me know if there are other factors preventing this.  

  • 1
    Avatar
    Zach Kathnelson

    Hi Guys - sounds like you should be able to accomplish what you're looking for with the SWITCH function.  SWITCH let's you execute a completely different formula based on a variable value (ie., based on what a user has selected in a dropdown).  So, Trudy, if a User chooses Alpha, you can show column C:C.  If they choose Beta, you can show column D:D.  

    SWITCH ($Greek Letter, "Alpha", C:C, "Beta", D:D).  This would work even if C:C and D:D are from completely different Data Sources.  

    Let me know if there are other factors preventing this.  

  • 0
    Avatar
    T V

    Zach - thank you! The switch() worked. I had tried it before, but I had the input parameters incorrect. It's working now.

     

  • 0
    Avatar
    Chanly Nguyen

    Zach,


    How am I able to SWITCH () through 600 Stocks (My table has that many columns.

    Down the table is a timesries of the stock prices, and the end user would like to be able to use a drop down (or type in editor) to select a stock and switch to that column of data.

    I gave up after like about 10 stocks


    SWITCH($Variable,"Stock A", Column A,"Stock B", Column B,"Stock C", Column C) etc etc to 600th

Please sign in to leave a comment.