+ Post New Thread
Results 1 to 14 of 14
Office Software Thread, Displaying a selected row of data vertically in Technical; I have an excel sheet outputted from SIMS.net of some 150 rows of student data with filters applied. The filters ...
  1. #1

    Join Date
    Jan 2013
    Location
    UK
    Posts
    7
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Angry Displaying a selected row of data vertically

    I have an excel sheet outputted from SIMS.net of some 150 rows of student data with filters applied.
    The filters are used by tutors to select down to their tutor groups and then the individual data record (row) of interest.
    Because of the width of the screen and a sheet with 80 plus columns it is difficult to assimilate the data once selected.
    I would like to select a number of columns and display them vertically with their corresponding column headers. See second worksheet called Data Display.
    I had thought that pivot tables would work but have not been successful, any ideas?
    Attached Files Attached Files

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Copy > Paste Special > Transpose

  3. #3

    Join Date
    Jan 2013
    Location
    UK
    Posts
    7
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by LosOjos View Post
    Copy > Paste Special > Transpose
    Thanks LosOjos,

    I had tried that. It allows you to put the data vertically, it responds to the filtering and does not pick up hidden columns but you cannot place the relevent fields side by side!
    Also it requires your user to carry out the transpose and not all of them are that IT literate. What I would like to a achieve is a SELECT student on sheet one, go to second sheet to see their data sensibly displayed.

    More ideas welcome!

  4. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    How are you with macros? Sounds to me like the best solution would be to put all the data in a Pivot so that the user can double click a student to open their data in a seperate sheet, at which point you'd need a macro to automate the transpose routine for them.

    I can't really think of another way of doing it

  5. Thanks to LosOjos from:

    drowningindata (14th January 2013)

  6. #5

    Join Date
    Jan 2013
    Location
    UK
    Posts
    7
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by LosOjos View Post
    How are you with macros? Sounds to me like the best solution would be to put all the data in a Pivot so that the user can double click a student to open their data in a seperate sheet, at which point you'd need a macro to automate the transpose routine for them.

    I can't really think of another way of doing it
    Thanks again, I can see that working, only downside I can see is that each teacher will end up spawning 25 new worksheets, I wonder if I can get the macro to name the sheet for them and ensure that the master sheet stays in view?

  7. #6

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by drowningindata View Post
    Thanks again, I can see that working, only downside I can see is that each teacher will end up spawning 25 new worksheets, I wonder if I can get the macro to name the sheet for them and ensure that the master sheet stays in view?
    Alternatively you could make the master sheet delete any other sheets when it's selected - whatever you do, it's probably going to require a degree of training for the less computer-savvy staff!

  8. #7


    Join Date
    May 2009
    Posts
    3,395
    Thank Post
    301
    Thanked 917 Times in 684 Posts
    Rep Power
    346
    Use a combination of lookups. See attached. I've added some numbers below the headings which you can then use with hlookup against the subject. That hlookup returns a number which is the offset for a vlookup. Vlookup finds the row that the student is in and uses the result of the hlookup as the offset.

    (click into the name cell and use the drop down to change the name and the results magically change).
    It's more complicated than it sounds!
    Attached Files Attached Files
    Last edited by pcstru; 14th January 2013 at 03:20 PM.

  9. Thanks to pcstru from:

    drowningindata (14th January 2013)

  10. #8

    Join Date
    Jan 2013
    Location
    UK
    Posts
    7
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by LosOjos View Post
    Alternatively you could make the master sheet delete any other sheets when it's selected - whatever you do, it's probably going to require a degree of training for the less computer-savvy staff!
    RE:make the master sheet delete any other sheets when it's selected

    How would I do that?

  11. #9

    Join Date
    Jan 2013
    Location
    UK
    Posts
    7
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by pcstru View Post
    Use a combination of lookups. See attached. I've added some numbers below the headings which you can then use with hlookup against the subject. That hlookup returns a number which is the offset for a vlookup. Vlookup finds the row that the student is in and uses the result of the hlookup as the offset.

    It's more complicated than it sounds!
    Sounds interesting.......I am new to this forum!!.....but I cannot locate your attachment?

  12. #10

    Join Date
    Jan 2013
    Location
    UK
    Posts
    7
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by drowningindata View Post
    Sounds interesting.......I am new to this forum!!.....but I cannot locate your attachment?

    Have now found it!! I could swear it was not there before!!

  13. #11

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,771
    Thank Post
    1,511
    Thanked 1,294 Times in 884 Posts
    Rep Power
    813
    Quote Originally Posted by drowningindata View Post
    RE:make the master sheet delete any other sheets when it's selected

    How would I do that?
    @pcstru's solution sounds simpler but just in case: you'd have to override the master sheet's activate event, cycling through all the sheets and deleting any that weren't the master sheet. Here is the code FYI:

    Code:
    Sub WorkSheet_Activate()
    Dim s As Worksheet
    Dim blAlerts As Boolean
        
        blAlerts = Application.DisplayAlerts
        Application.DisplayAlerts = False           'disable prompts so no confirmation is needed to delete a sheet
        
        For Each s In ThisWorkbook.Sheets           'cycle all worksheets in workbook
            If s.Name <> Me.Name Then s.Delete      'delete any whose name does not match current sheet
        Next s
        
        Application.DisplayAlerts = blAlerts        'enable alerts again (if they were previously enabled)
    End Sub
    EDIT: should have mentioned, that code must go in the master sheet's module in VBA (it'll be named after the sheet, for example if the sheet is called 'Master' then the module will be called 'Sheet1 (Master)' or similar (the number may vary))

    EDIT2: also should mention, this will delete all sheets except the one containing this code, so if there were other sheets in your workbook you wanted to keep, you'd need to specify additional if/else conditions to retain them (or a 'Select' statement depending on the complexity, but now I'm over complicating matters!)
    Last edited by LosOjos; 14th January 2013 at 03:41 PM.

  14. Thanks to LosOjos from:

    drowningindata (15th January 2013)

  15. #12


    Join Date
    May 2009
    Posts
    3,395
    Thank Post
    301
    Thanked 917 Times in 684 Posts
    Rep Power
    346
    Quote Originally Posted by pcstru View Post
    It's more complicated than it sounds!
    Err ... or maybe less.

  16. #13


    Join Date
    May 2009
    Posts
    3,395
    Thank Post
    301
    Thanked 917 Times in 684 Posts
    Rep Power
    346
    Quote Originally Posted by drowningindata View Post
    Have now found it!! I could swear it was not there before!!
    Mmm. Let me know how you get on.

  17. #14

    Join Date
    Jan 2013
    Location
    UK
    Posts
    7
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Hi Guys,
    Just to let you know that a variation on the VLOOKUP with offsets has resulted in a solution I am pleased with and will try out on some tutors and students tomorrow.

    Thank you both for your help.



SHARE:
+ Post New Thread

Similar Threads

  1. Best way to store 20TB of Data?
    By BackCat3 in forum Hardware
    Replies: 17
    Last Post: 20th May 2011, 12:53 PM
  2. [MS Office - 2003] Excel - Creating lists of data
    By gmiller in forum Office Software
    Replies: 3
    Last Post: 1st March 2011, 04:25 PM
  3. Making effective use of data held in an MIS
    By MikeBostock in forum MIS Systems
    Replies: 3
    Last Post: 9th October 2007, 08:17 PM
  4. Replies: 18
    Last Post: 19th June 2007, 10:02 AM

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Posting Permissions

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