+ Reply to Thread
Results 1 to 6 of 6

Changing Horizontal to vertical references

  1. #1
    Registered User
    Join Date
    01-02-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Changing Horizontal to vertical references

    In Excel 2003, is there an easy way to change horizontal cell reference to Vertical cell reference?

    For example i have 31 sheets, each for the day of the month, and in the first page i have a monthly summary. of numbers from these 31 sheets.

    so i have:

    5 10 5 5 20 5 5 (costing) from day1 (sheet) for example and associated references for these numbers are J81, k81, l81, m81, n81...r81 etc. These are displayed horizontally

    i want to copy thse numbers AND Most importantly References onto the monthly summary sheet table which is my first sheet, but vertically

    so i want to paste this reference into the monthly summary (first sheet) to references:

    J5, j6, j7,j8,j9,j10 etc

    so it displays like this:

    5 = j81 (of second sheet)
    10 = k81
    5 = etc
    5
    20
    5
    5

    without doing it for every single cell which takes so long.

    Thanks all.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Changing Horizontal to vertical references

    I'm guessing that you've simpified your example to make it easier to explain.
    If that's true, you'll really help us more by either
    a) posting a sample workbook that demonstrates your situation
    or
    b) describing exactly what you need to do, posting some sample data, and posting the formulas you've tried (even if they didn't work).
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    01-02-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Changing Horizontal to vertical references

    Ok here is the Sheet im working on.

    If you go to the first sheet (Monthly summary) theer is essentially a table that summaries all the costs in the other 31 sheets (each representing a day of the month)

    What i am trying to do is, for example in the sheet ('1') (day 1) im trying to reference cells j81 to r81 into the 'monthly summary' sheet into cells J5-J13.

    And i want to do that for each day of the month, i.e. 31 sheets, so for day 2 of the month (2!) i want to put j81-r81 into "monthly summary" K5-K13 and so on for each day.

    And i have 3 tables to fill. so as u can imagine it would take ridiculously long to reference each cell individually from each other 31 sheets. I treid something with index in the K column but its wrong :S

    Thanks for your help, it is really appreciated.
    Attached Files Attached Files
    Last edited by Seriously; 01-02-2010 at 11:39 PM.

  4. #4
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Changing Horizontal to vertical references

    Thanks for posting the workbook....That helped.

    Try this on the Monthly Summary sheet

    Please Login or Register  to view this content.
    Copy that formula down through J12

    Is that something you can work with?

  5. #5
    Registered User
    Join Date
    01-02-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Changing Horizontal to vertical references

    Fantastic, thankyou so much kind sir.

  6. #6
    Registered User
    Join Date
    07-21-2010
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Changing Horizontal to vertical references

    Hello,

    I think I have a similar question. I need to move data from a horizontal line in sheet 2 to a vertical line in sheet 1. That by itself I can do with the transpose funtion. The problem is that I have many horizontal lines on sheet 2 and I don' t want to have to type a new transpose formula for each line.

    The trick I don't know is how, when copying this formula, I can get Excel to automatically change the cell references vertically. It seems by default to want to change the cell references horizontally. Unless you add the $A$1 to fix the cell to be referenced.

    If there's a quick answer to this I'd love to hear it. I'll be posting a sample spreadsheet in a bit in case the question isn't clear.

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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