VLOOKUP conundrum!

[norfox]

Hi.

I seem to have outsmarted myself by over-relying on VLOOKUP in a spreadsheet, and wonder if anyone can think of a way to help.

In essence, I have an Excel file with multiple sheets that contain information about teaching staff. For example one sheet has an entire year's timetable, another has marking allocations, another has tutorial allocations.
I have a main summary sheet with a dropdown list of the teaching staff and, depending on the staff member selected, the totals boxes on the summary sheet show the relevant totals for that individual. This I've done by linking the dropdown list selection on the summary sheet with cell A1 on each of the individual sheets, which then drives the VLOOKUP on those individual sheets accordingly (and feeds into the totals on the summary sheet, if that makes sense).

This works great, and I'm able to sit down with individual teachers and easily show them how their time is allocated.

What I can't do, though, is draw up a summary table of totals for all teachers. I've shot myself in the foot because the output can only ever relate to the individual selected in the dropdown list.
Yes, I can manually transpose the figures into a summary table for all teachers by going through each individual and noting their totals down, but I wondered if anyone could think more laterally than me and work out a fairly straightforward solution. Any help gratefully received, and apologies if that wasn't particularly clear.

 

[kweaver]

Welcome to Mr. Excel...

It sounds like a VBA solution, but it would be most helpful if you used XL2BB and provided some data examples.

 

[offthelip]

You could write a bit of vBA to loop through your list of teachers , putting each teachers name into the relevant cell on the summary sheet, then copy the summary data for that teacher to a row in another sheet, then carry on with the next teacher and putting in the next row. This would be easy to write. All that is needed is where the list of the teachers is, where the name has to go and where the data is displayed and where you want the summary

 

[norfox]

Hi. Thanks both for replying.
I did wonder about VBA and recorded a macro that did exactly as you suggest, but my problem was with the macro not recognising that I'd used the dropdown box (and therefore my selection) so I discounted this idea. From what you say, am I able to stipulate the entry for that cell in the VBA code, and it could therefore copy the results accordingly?

Many thanks

 

[offthelip]

If you answer the questions I put I can write the code for you:
Which cell is your drop down in?
where is the list which creates the drop down?
Where are the cells which display the data for each teacher
Would writing the results to a new worksheet be acceptable

 

[norfox]

Hi offthelip

That is really kind - thank you.

The dropdown is in X25
The list it's derived from is from A1 to A34
There are four data display cells - C25, C39, F25 and F39
To a new worksheet would definitely be acceptable

Many thanks

 

[offthelip]

try this code:

Sub test()
Dim outarr()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
listarr = Range(Cells(1, 1), Cells(lastrow, 1))

ReDim outarr(1 To lastrow, 1 To 5)
indi = 2
For i = 2 To lastrow
Cells(25, 24) = listarr(i, 1)
     outarr(indi, 1) = listarr(i, 1)
     outarr(indi, 2) = Cells(25, 3)
     outarr(indi, 3) = Cells(39, 3)
     outarr(indi, 4) = Cells(25, 6)
     outarr(indi, 5) = Cells(39, 6)
   indi = indi + 1
Next i
Worksheets.Add
Range(Cells(1, 1), Cells(lastrow, 5)) = outarr

End Sub

 

[norfox]

Hi offthelip

Many thanks - I'll give this a try in the morning. Brain is fried tonight!
Really appreciate your help.

 

[norfox]

Hi offthelip

Brain was more in gear today, so have given this a try!
What I found was that it listed the individual teachers on the new worksheet, but that the values in the data display cells associated with each teacher were the same, i.e.

Teacher Data1 Data2 Data3 Data4
A

 

[norfox]

Hi offthelip

Brain was more in gear today, so have given this a try!
What I found was that it listed the individual teachers on the new worksheet, but that the values in the data display cells associated with each teacher were the same, i.e.

Teacher Data1 Data2 Data3 Data4
A 54 65 32 34
B 54 65 32 34
C 54 65 32 34

It was picking up the data from that which was currently displaying on the worksheet, and not moving on to the data associated with the relevant teacher, if that makes sense.

Sorry, I don't want to take up any more of your time, so please feel free to disregard this!
Many thanks