+ Reply to Thread
Results 1 to 5 of 5

Transpose calculated value from horizontal to vertical?

  1. #1
    Forum Contributor
    Join Date
    04-01-2010
    Location
    USA
    MS-Off Ver
    Office 2021
    Posts
    185

    Transpose calculated value from horizontal to vertical?

    I have a series of cash flows as follows:

    Year 1 Year 2
    Income 100 150
    Expense 20 30
    Net Income 80 120

    I need to take the calculated "Net Income values" and also place them vertically by themselves, like this:

    Year 1 80
    Year 2 120

    I know I can copy and paste the values easily using the "paste special... transpose", but how can I easily do this so that it continues to reference the original values? So that if I change the Income in year 1 of 100 to 120 the vertical Year 1 value would change from 80 to 100?

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Transpose calculated value from horizontal to vertical?

    Do this..

    - Copy your entire range of data
    - Select destination cell
    - Paste special -- transpose -- formulas
    Ok
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Transpose calculated value from horizontal to vertical?

    you get better help if you folow the advice in the link below.

    http://www.excelforum.com/the-water-...-question.html
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Contributor
    Join Date
    04-01-2010
    Location
    USA
    MS-Off Ver
    Office 2021
    Posts
    185

    Re: Transpose calculated value from horizontal to vertical?

    example for excelforum transpose.xlsx

    I tried the transpose and formulas under paste special, but it didn't work. I'd like the Net Income values that are horizontal to be placed vertically in the column next to the years in the example.

  5. #5
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Transpose calculated value from horizontal to vertical?

    With that layout you could use

    In B8
    =INDEX($4:$4,,MATCH("Year "&A8,$1:$1,0))
    Note: Year 10 and Year 11 have a single space between year & no. of year while others have two. Get this amended

    or if years are always chronological in both arrays
    =INDEX($B$4:$U$4,A8)

    Copy down

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Transpose Vertical Data to Horizontal
    By Randu555 in forum Excel General
    Replies: 5
    Last Post: 04-18-2013, 05:05 PM
  2. [SOLVED] Need help transpose Texts and Numbers from Horizontal to vertical to the last Row
    By tuongtu3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-07-2013, 01:51 PM
  3. [SOLVED] Transpose Horizontal to Vertical with ID Column
    By galaxycoff in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-04-2012, 10:16 PM
  4. [SOLVED] convert vertical to horizontal (without Transpose)
    By timtim89 in forum Excel General
    Replies: 6
    Last Post: 03-28-2012, 10:50 AM
  5. [SOLVED] transpose data from horizontal to vertical in a specific column
    By elaine in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2006, 12:10 PM

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