[ms office - 2010] Displaying a selected row of data vertically

[drowningindata]

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?

Sample data - December 2012 - Progress Check.xlsx

[LosOjos]

Copy > Paste Special > Transpose

[drowningindata]

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!

[LosOjos]

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

[drowningindata]

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?

[LosOjos]

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!

[pcstru]

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!

Sample data - December 2012 - Progress Check.xlsx

Edited January 14, 2013 by pcstru

[drowningindata]

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?

[drowningindata]

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?

[drowningindata]

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!!

[LosOjos]

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:

 

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!)

Edited January 14, 2013 by LosOjos

[pcstru]

It's more complicated than it sounds!

 

Err ... or maybe less.

[pcstru]

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

 

Mmm. Let me know how you get on.

[drowningindata]

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.