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