Creating horizontal view for existing sheet

Hi,

I'm trying to get sheet A (vertical view) into sheet B view (horizontal view).


Sheet A:


Sheet B:


the helper column date is in txt/number so it would match sheet A.

Could anyone help?

Thanks. :)

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 07/15/22 Answer ✓

    Hi @Christina09

    I believe I responded on this other post, here!

    I'll repeat my response in case anyone else is looking at this thread:


    You can use the third part of the INDEX function to identify what column you want to bring data back from.

    Here's the structure of an INDEX function:

    =INDEX({Range with Data to Return}, Row Number, Column Number)

    In your instance, the first range would be the entire sheet, or all of your columns. Click on one column name, then hold the Shift button down and click your last column name.


    Then for the Row Number you can manually put this in, since you only have one row per Stock.

    =INDEX({Whole Sheet}, 2,

    For the last bit of the formula, we'll use a MATCH function to match the date in your helper column to the correct top row, so we can find the right column:

    MATCH([Helper Date]@row, {Top Row}, 0)


    For a full formula:

    =INDEX({Whole Sheet}, 2, MATCH([Helper Date]@row, {Top Row}, 0))


    For Stock B, then you'll only need to change the row 2 in the middle to the number 3:


    And so on. Let me know if this works for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    edited 07/15/22 Answer ✓

    Hi @Christina09

    I believe I responded on this other post, here!

    I'll repeat my response in case anyone else is looking at this thread:


    You can use the third part of the INDEX function to identify what column you want to bring data back from.

    Here's the structure of an INDEX function:

    =INDEX({Range with Data to Return}, Row Number, Column Number)

    In your instance, the first range would be the entire sheet, or all of your columns. Click on one column name, then hold the Shift button down and click your last column name.


    Then for the Row Number you can manually put this in, since you only have one row per Stock.

    =INDEX({Whole Sheet}, 2,

    For the last bit of the formula, we'll use a MATCH function to match the date in your helper column to the correct top row, so we can find the right column:

    MATCH([Helper Date]@row, {Top Row}, 0)


    For a full formula:

    =INDEX({Whole Sheet}, 2, MATCH([Helper Date]@row, {Top Row}, 0))


    For Stock B, then you'll only need to change the row 2 in the middle to the number 3:


    And so on. Let me know if this works for you!

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!