Hi,
I'm working a timetable for instrumental teaching staff, and whilst I can pull relevant data from seperate sheets to give a staff timetable (on a preformatted sheet), I'm struggling to get a SCHOOL timetable (of services WE supply.
The raw data sheet contains the following columns:
A school names
B total hours the school buys
C-V breakdown of types of delivery and their associated hour allocation
My problem is in this formula:
=OFFSET(MATCH(Z3,A3:A123,0),(MATCH(Z3,A3:A123,0),0,0)))
1.The basic formula should match the schools name, search the row for the first occupied cell and return that value
2.The advanced formula should match the schools name,and search for all the occupied cells returning their values.
This formula works:
=INDEX(A3:V123,MATCH(Z3,A3:A999,0)+0,10)
but the row offset (10) is fixed and needs to be variable (and ideally multiple!).
I have spent many hours applying various logic processes but to no avail (my BBC basic programming skills of old hold no sway here!). Please explain your working out for me so I can do it on my own next time
Many thanks
G
I'm working a timetable for instrumental teaching staff, and whilst I can pull relevant data from seperate sheets to give a staff timetable (on a preformatted sheet), I'm struggling to get a SCHOOL timetable (of services WE supply.
The raw data sheet contains the following columns:
A school names
B total hours the school buys
C-V breakdown of types of delivery and their associated hour allocation
My problem is in this formula:
=OFFSET(MATCH(Z3,A3:A123,0),(MATCH(Z3,A3:A123,0),0,0)))
1.The basic formula should match the schools name, search the row for the first occupied cell and return that value
2.The advanced formula should match the schools name,and search for all the occupied cells returning their values.
This formula works:
=INDEX(A3:V123,MATCH(Z3,A3:A999,0)+0,10)
but the row offset (10) is fixed and needs to be variable (and ideally multiple!).
I have spent many hours applying various logic processes but to no avail (my BBC basic programming skills of old hold no sway here!). Please explain your working out for me so I can do it on my own next time
Many thanks
G
