creating a summary sheet with reference to other sheets in the workbook

Hello - 

I am trying to create a summary sheet in a workbook, and trying to get a formula to recognize the data entered into a cell as a sheet name.  Without getting very specific, I have 6 boxes that each acquire a number of items each quarter.  Individuals are randomly assigned a box each quarter.  My sheet names refer to the box numbers, and each sheet records the number of items acquired in each box for the quarter.  The summary page transfers the number per box to the number per individual for each quarter, and then keeps a year to date total for each individual.   Also, individuals in the group can change each quarter.

 

So each individual has a column for box number, and a column for number of items under each quarter.  I am trying to pull the number of items from the sheet for the box number, but cannot figure out how to get the formula to recognize the data in the column as the sheet name.

 

If anyone can make sense out of that, I would greatly appreciate input.  And if I am making this much to difficult, feel free to point that out as well!

 

Thanks,

Barb

 

Sheet ="box 3"

year	2018
Period	Assigned to	use date	items
q1	lis	1/1-3/31	6
q2	janet	4/1-6/30	7
q3	janet	7/1-9/30	7
q4	lis	10/1/12/31	6

 

Sheet = summary page

	Q1		Q2		Q3		Q4
individual	box #	items	b #	items	b#	items	b#	items	YTD
joe	1	0	1	2	2		2		2
janet	2	7	3		3		1		7
lis	3		2		1		3		0
student 1	123456	#REF!							#REF!
student 2			123456	#REF!					#REF!
student 3									0