Copy > Paste Special > Transpose
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?
Copy > Paste Special > Transpose
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!
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
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!
Last edited by pcstru; 14th January 2013 at 03:20 PM.
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:
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))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
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.
drowningindata (15th January 2013)
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)