+ Reply to Thread
Results 1 to 6 of 6

IF/TRANSPOSE formula not transposing the column into the row

  1. #1
    Registered User
    Join Date
    06-05-2019
    Location
    Washington
    MS-Off Ver
    16.23
    Posts
    8

    IF/TRANSPOSE formula not transposing the column into the row

    I am trying to use the IF function to transpose the data in a column of one sheet into a row of another sheet (See attached example) The formula I'm using on the 'Qualifications' sheet in cells B2:H2 is the one I am having trouble with. For some reason, it's only grabbing the data in cell A2 of the 'Shifts' sheet. What am I doing wrong?
    Attached Files Attached Files
    Last edited by ocallaj; Yesterday at 06:45 PM. Reason: Solved

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365 Insiders
    Posts
    10,829

    Re: IF/TRANSPOSE formula not transposing the column into the row

    Try this formula in B2 and fill across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    According to Help files TRANSPOSE is to be committed by selecting the entire output range and then commit with Ctrl + Shift + Enter instead of just Enter. I disagree with that whole range method of committing array formulas for a host of reasons. Wrapping INDEX around your formula (as above) eliminates the need for Ctrl + Shift + Enter.
    Dave

  3. #3
    Registered User
    Join Date
    10-30-2019
    Location
    Bistrita, Romania
    MS-Off Ver
    2013
    Posts
    113

    Re: IF/TRANSPOSE formula not transposing the column into the row

    Pete_UK's solution for transposing that he posted here: https://www.excelforum.com/excel-gen...ml#post5229992
    seems appropriate for your issue too:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Put that in B2 and drag to the right. No IF and no array formula needed.

    Credits to Pete_UK.
    Show your appreciation - click ★ Add reputation!

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)
    Posts
    4,304

    Re: IF/TRANSPOSE formula not transposing the column into the row

    Use the array-entered (enter using Ctrl-Shift-Enter) formula in cells B2:G2 (Select all cells first, then paste the formula, and press Ctrl-Shift-Enter)

    =TRANSPOSE(Shifts!$A$2:$A$7)

    Or enter this as a regular formula in cell B2:

    =INDEX(Shifts!$A$2:$A$7,COLUMN(A1))

    and copy to C2:G2.

    Both these assume you don't want "Shifts" copied over from A1...
    Bernie Deitrick
    Excel MVP 2000-2010

  5. #5
    Registered User
    Join Date
    06-05-2019
    Location
    Washington
    MS-Off Ver
    16.23
    Posts
    8

    Re: IF/TRANSPOSE formula not transposing the column into the row

    Thank you all! Those solutions all worked for me. My issue is solved.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365 Insiders
    Posts
    10,829

    Re: IF/TRANSPOSE formula not transposing the column into the row

    You are welcome. Glad to help. Thank you for the feedback, added rep and marking your thread Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1