capture data on multiple sheets to put into one table

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
Each employee gets a sheet and in the same workbook i have a table on sheet 1 that sums up all the data and a chart above it that bar graphs a few peices of that data.

This works fine but other offices would like to use the same type of file but they have different people and different numbers of employees. I have added a "new sheet" macro that duplictes the master sheet as many times as needed and then that office can simply fill in the new employees data. What i can figure out is the following two items

1. How the table on sheet one can look across the whole workbook and capture the data neccessary for ALL sheets. My sheet tracks 7 people but another office may have 50 so my hard coded table wont work. It needs to dynamically lookup the info

2. once i get the data into a table i need to chart a few pieces. Again right now I have the chart hard coded based on my old design with only 7 employees. I would need the new chart to see the data on this dynamically created table and then produce a new chart.

Any help on either of these issues is greatly appreciated.
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Your second question is difficult to answer without knowing more about the relationship between the data and the graph... some more detail woul dbe helpful.

It's possible that you could deal with your first question using a 3-D reference... can you post a small sample of data for a singl eemployee, and also a sample of how you summarize this data on the first sheet?
 

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
here is the original page of data that each employee has. You see it documents some of the basic info and then on A11:E11 each job is entered and the important data for each is listed. The data that is listed on tha range is then calculated in varius cells in G3:G8 and J3:J8. On a real emplyee there will a few entries each day. I have it set for the formulas to run through row 1000
Test reporter schedule pages 101006.xls
ABCDEFGHIJK
1A1-CourtONBOOK
2FirstCourtTotalPagesOutStatCourtAVGStatCourtAVG
3LastReporter0PgsOut0MaxOut0
4Contact1904-555-1212PgsPD0 %PD0% 
5Contact2904-555-1212TotalPagesPDTotal0AvgOnTPgNA
6Fax904-555-12120PgsIn372AvgestDiff-1%
7eMail0TtlBank372 #ofjobs7 
8TODAY10/10/2006AvgIn6 Pgsperjob53 
9DateTakenJob#Est.PagesDateRcvdAct.Pages%DiffAVGINMAXOUTPDPGSACT+ESTACTPGS
10737010/10/2006372-1%600372372
115/3/2006800462955/15/2006961%8009696
125/10/20061842361405/19/20061529%700152152
135/15/200679905705/15/200600%00000
145/16/200661800505/16/200600%00000
155/22/2006804394605/30/20066712%6006767
A1-Court


Then on my chart page i have the following table that compiles the data and as well as finds the avg/median value for each data. this table is then used to poulate a chart (which i cant seem to get the HTML add in to copy) the chart shows a bar that tracks three things. Pages outstanding = C24 and the pages pd= c25. its a combo bar that shows all total pages with the portion of the bar representing past due in red.<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=5><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - reporter schedule pages.xls</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: 11.0 : OS = Windows Windows 2000</FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=5><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb059465><INPUT onclick='window.clipboardData.setData("Text",document.formFb543420.sltNb142651.value);' type=button value="Copy Formula" name=btCb290334></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=5><TABLE border=0><TBODY><TR><FORM name=formFb543420><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb543420.txbFb965317.value = document.formFb543420.sltNb142651.value" name=sltNb142651><OPTION value==TODAY() selected>B22<OPTION value==Gina!B2-Chart!C25>C24<OPTION value==Georgia!B2-Chart!D25>D24<OPTION value==Kim!B2-Chart!E25>E24<OPTION value==Gina!J5>C25<OPTION value==Georgia!J5>D25<OPTION value==Kim!J5>E25<OPTION value==SUM(C24:C25)>C26<OPTION value==SUM(D24:D25)>D26<OPTION value==SUM(E24:E25)>E26<OPTION value==Gina!E5>C27<OPTION value==Georgia!E5>D27<OPTION value==Kim!E5>E27<OPTION value==C27+C26>C28<OPTION value==D27+D26>D28<OPTION value==E27+E26>E28<OPTION value==TRUNC(Gina!H5,)>C29<OPTION value==TRUNC(Georgia!H5,)>D29<OPTION value==TRUNC(Kim!H5,)>E29<OPTION value==IF(ISERROR(Gina!I5),0,Gina!I5)>C30<OPTION value==IF(ISERROR(Georgia!I5),0,Georgia!I5)>D30<OPTION value==IF(ISERROR(Kim!I5),0,Kim!I5)>E30<OPTION value='=(COUNTIF(Gina!$H7:$H1001,">8")/(COUNTIF(Gina!$E7:$E1001,">0")))'>C31<OPTION value='=(COUNTIF(Georgia!$H7:$H1001,">8")/(COUNTIF(Georgia!$E7:$E1001,">0")))'>D31<OPTION value='=(COUNTIF(Kim!$H7:$H1001,">8")/(COUNTIF(Kim!$E7:$E1001,">0")))'>E31<OPTION value==Gina!E6>C32<OPTION value==Georgia!E6>D32<OPTION value==Kim!E6>E32<OPTION value==IF(ISERROR(Gina!F5),0,Gina!F5)>C33<OPTION value==IF(ISERROR(Georgia!F5),0,Georgia!F5)>D33<OPTION value==IF(ISERROR(Kim!F5),0,Kim!F5)>E33<OPTION value==Gina!B5>C34<OPTION value==Georgia!B5>D34<OPTION value==Kim!B5>E34<OPTION value==C28/C34>C35<OPTION value==D28/D34>D35<OPTION value==E28/E34>E35</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value==TODAY() name=txbFb965317></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>B</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>C</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>D</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle><CENTER>E</CENTER></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>22</CENTER></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">10/10/2006</FONT></TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #d4d0c8 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>23</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right"> </TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: left">emp 1</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: left">emp 2</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: left">emp 3</TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>24</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: left">Pgs Out</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">0</FONT></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">160</FONT></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: right">0</FONT></TD></TR><TR><TD style="BORDER-TOP: #000000 0.5pt solid; FONT-SIZE: 10pt; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; FONT-FAMILY: menu; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%"><CENTER>25</CENTER></TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt; VERTICAL-ALIGN: bottom; BORDER-LEFT: #000000 0.5pt solid; COLOR: #000000; BORDER-BOTTOM: #000000 0.5pt solid; FONT-FAMILY: Arial; BACKGROUND-COLOR: #99ccff; TEXT-ALIGN: left">Pgs PD</TD><TD style="BORDER-RIGHT: #000000 0.5pt solid; FONT-SIZE: 10pt;
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
I am afraid that the only real way to go about making this flexible as you want is to basically make a button that says "Add Emplyee", and when pressed, the code behind it adds a new sheet, copies and pastes from a template sheet, then makes the proper entries in the summary and chart sheets. I can't think of an easy way...
 

Smithgall

Board Regular
Joined
May 24, 2006
Messages
68
still working on it

hatman, yeah that is where i am stuck. the add employee button is what i have now. It does copy the master template sheet and adds it to the end. however what i cannot figure out is how to go back to the table and add a new collumn based the new employee and then tell thechart there is a new collumn and adjust its view to now chart that new employee. I may have to go back to simply providing 50 blank sheets an hard coding a table to read all of them. I am working a way to allow the user to tell the chart what to read. Ultimately the chart is what the user reads. the table is just a nice way to see all the emplopyes data in one spot.
 

Forum statistics

Threads
1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top