Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Take horizontal info from one worksheet and display vertical

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Netherlands
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have been able to take data from worksheet 1 and with the help of formulas brought it over to worksheet 2. The information on worksheet 1 is in row #1 and columns A through to G, and displayed on worksheet 2 in column A, Rows 1 though to 7. (ie. The info on worksheet 1 is horizontal and displayed on worksheet 2 vertically.) Now when I copy the 7 rows on worksheet #2 and paste them to the next 7 rows, rows 8 through to 14, the formula should pertain to row #2 on worksheet #1, but it is not. I hope this is clear enough. Please help

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    transpose function might be worth looking at it will re aling for you
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Netherlands
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There is formulas involved, and the transpose function throws these all out of wack. Is there any other way?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    are you positive you've worded this correctly ?

    I'm trying to replicate it and it looks nearly impossible

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    if you anchor they will stay ok, if not they might WACK as you say..

    lost transpose dont re function the cells it will re align them as you want..

    im lost now!


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  6. #6
    New Member
    Join Date
    May 2002
    Location
    Netherlands
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The grid on worksheet 2 is actually 7 rows by 12 columns and there are numerous formulas in each cell that are referencing to cells in worksheet 1. The formulas include 'search', 'concatenate', 'if' statements, etc. The entire grid on worksheet 2 only pertains to 1 row and 7 columns on worksheet 1. I put a dollar sign in each formula before the letter designation for the column, because these are constant, but I can not put the dollar sign in front of the number for the row designation, because the next 7 by 12 grid will be pertaining to row #2 on worksheet 2. I hope this clears it up a bit.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Firstly, the reason you're having this problem is because of the interactions of the layouts of the info you require

    but hey, we all start somewhere, or all inherit bad designs from various people

    My honest advice in this scenario is to copy and paste your grid down and then manually edit the rows in the new formulae

    so where you have a 1, change it to an 8 etc etc in all the cells

    Hopefully, this will be the most efficient way if you're only copying it down a few times

    if, though, you're having to do this quite a few times (liek 20 or 30) I suspect you're in trouble....

    with basic links like this, we can use =INDIRECT and tie it to the rounded up row number divided by 8

    however, this would need to be incorperated into the fact that your switching from vertical to horizontal

    further to that little twist is the fact that you're not just linking : you have IFs and CONCATENATIONS and SEARCHES in there also
    which make a "global" solution based on blind info virtually impossible

    Looking on the bright side though : one of the best lessons to learn is the need to look ahead in the initial design process so you don't paint yourself into a corner : a bit like snooker players, they look to see where their cue ball will end up *after* they've played their shot before they actually play the shot

    if the manual method isn't viable for you, email me your spreadsheet and I'll take a look at it - although it may be to say "no, I'm not capable of solving this"

    novulari@hotmail.com
    :: Pharma Z - Family drugstore ::

  8. #8
    New Member
    Join Date
    May 2002
    Location
    Netherlands
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, chris. I think you're right, I probably have to re-think how I am going to do this. Thanks for the help though guys!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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