i dont even know where to start

[degett]

Hey, im not the greatest at excel, and i need to make a formula that tabulates stats.

i would need each agent to be calculated month-to-date from stats on different excel sheets..

ok basically i have two different excel documents that calculate daily stats for two different campaigns.. they have the formulas on the summary tab of their respected excel sheets that takes the data from other tabs in that same sheet and they work fine.. now what i need is to create a NEW excel sheet that takes the data from the two summary tabs in the two different excel documents and puts them together into one totals summary page. i dont know if that makes sense to you guys, but i've been racking my brain at it for a week and im nowhere

 

[degett]

bump anyone have any ideas?

 

[Magriza]

Hi and welcome to the board. I'd suggest you define your problem a little more clearly. When you say you have to create a new workbook and take the data from 2 other workbooks you don't mention if there are any limitations or contraints that you have to work within. If it's as simple as taking the data then simply create a new workbook and in a cell type in equals (=) before clicking on the other workbook and selecting the data you wish to display. If you delete the $ signs from the cell reference (change from absolute to relative referencing) you can then copy and paste the formula and it'll automatically pull the data.

However, if there's more you need to do then you should post a sample of each summary sheet, supply the workbook and sheet names and describe what your desired outcome is.

 

[degett]

Ok well ill try to explain a little better.

I have two excel workbooks. They both calculate daily stats for agents in a call center. i provide all the raw data for orders placed in the "orders" tab of each book, as well as provide everyones calls taken in the "Calls" tabs. the summary tab of each workbook takes all the raw data and displays it nicely as a table, showing each agent name in the rows, along with their total orders placed, and calls taken, along with a conversion %. so now i have two summary pages on two different workbooks that are filled in from formulas.. im worried if i just copy and paste the data, then the cells with mess up because that info is in fact form a formula itself. would that be an issue?


heres an example:
<TABLE style="WIDTH: 382pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=509><COLGROUP><COL style="WIDTH: 112pt; mso-width-source: userset; mso-width-alt: 5449" width=149><COL style="WIDTH: 93pt; mso-width-source: userset; mso-width-alt: 4534" width=124><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3840" width=105><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4790" width=131><TBODY><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 112pt; HEIGHT: 21pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68 height=28 width=149>Agent Name</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 93pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68 width=124>Total Orders</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 79pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68 width=105>Total Calls</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl68 width=131>Conversion %</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 112pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20 width=149>Agent1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl70>5</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; COLOR: #9c0006; FONT-SIZE: 11pt; BORDER-TOP: #e0dfe3; FONT-WEIGHT: 400; BORDER-RIGHT: #e0dfe3; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl67>40.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 112pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20 width=149>Agent2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl70>6</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; COLOR: #9c0006; FONT-SIZE: 11pt; BORDER-TOP: #e0dfe3; FONT-WEIGHT: 400; BORDER-RIGHT: #e0dfe3; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl67>33.33%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 112pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20 width=149>Agent3</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>1</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl70>2</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; FONT-FAMILY: Calibri; BACKGROUND: #c6efce; COLOR: #006100; FONT-SIZE: 11pt; BORDER-TOP: #e0dfe3; FONT-WEIGHT: 400; BORDER-RIGHT: #e0dfe3; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl67>50.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 112pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 height=20 width=149>Agent4</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65>7</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl70>10</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; FONT-FAMILY: Calibri; BACKGROUND: #c6efce; COLOR: #006100; FONT-SIZE: 11pt; BORDER-TOP: #e0dfe3; FONT-WEIGHT: 400; BORDER-RIGHT: #e0dfe3; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl67>70.00%</TD></TR></TBODY></TABLE>


this is what the summary tabs look like from each workbook. now if i had two of these pages and i wanted to combine them into 1 combining all data but making sure the right numbers go to the right agent, how would i do so? Does that better explain?

 

[butcherjb]

Hi

Would this help - assuming all data starts in A1

<TABLE style="WIDTH: 698pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=928 border=0 x:str><COLGROUP><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 3620" span=3 width=198><COL style="WIDTH: 251pt; mso-width-source: userset; mso-width-alt: 6107" width=334><TBODY><TR style="HEIGHT: 47.25pt" height=63><TD class=xl25 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; HEIGHT: 47.25pt; BACKGROUND-COLOR: white" width=198 height=63>Agent Name</TD><TD class=xl26 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ccccff; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: white" width=198>Total Orders</TD><TD class=xl26 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ccccff; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: white" width=198>Total Calls</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ccccff; WIDTH: 251pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: white" width=334>Conversion %</TD></TR><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD class=xl28 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: windowtext 1pt solid; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: white" width=198 height=35>Agent1</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: white" width=198 x:num="6">=Sheet1!B2+Sheet2!B2</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: white" width=198 x:num="12">=Sheet1!C2+Sheet2!C2</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 251pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: #ff99cc" width=334 x:num="0.5" x:fmla="=B2/C2">=B2/C2</TD></TR><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD class=xl28 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: windowtext 1pt solid; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: white" width=198 height=35>Agent2</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: white" width=198 x:num="5">=Sheet1!B3+Sheet2!B3</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: white" width=198 x:num="10">=Sheet1!C3+Sheet2!C3</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 251pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: #ff99cc" width=334 x:num="0.5" x:fmla="=B3/C3">=B3/C3</TD></TR><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD class=xl28 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: windowtext 1pt solid; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: white" width=198 height=35>Agent3</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: white" width=198 x:num="3">=Sheet1!B4+Sheet2!B4</TD><TD class=xl24 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 149pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: white" width=198 x:num="4">=Sheet1!C4+Sheet2!C4</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 251pt; BORDER-BOTTOM: #ccccff 1pt solid; BACKGROUND-COLOR: #ccffcc" width=334 x:num="0.75" x:fmla="=B4/C4">=B4/C4</TD></TR><TR style="HEIGHT: 26.25pt; mso-height-source: userset" height=35><TD class=xl31 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: windowtext 1pt solid; WIDTH: 149pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: white" width=198 height=35>Agent4</TD><TD class=xl32 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 149pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=198 x:num="16">=Sheet1!B5+Sheet2!B5</TD><TD class=xl32 style="BORDER-RIGHT: #ccccff 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 149pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: white" width=198 x:num="24">=Sheet1!C5+Sheet2!C5</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ccccff; BORDER-LEFT: #ccccff; WIDTH: 251pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ccffcc" width=334 x:num="0.66666666666666663" x:fmla="=B5/C5">=B5/C5


</TD></TR></TBODY></TABLE>

Very basic but is that what you want.

JB

 

[jc.021286]

Transferring the information is pretty simple.

if using excel 2007,
have both workbooks open.
hold shift and click each tab in 1 workbook, they should be selected now.

Right click the tab and select copy/move to

check the box that you want to create copies, and from the drop down box, change the destination to the other workbook.

>ok

Check out what happened. Any duplicate named sheets will be ___(2), adjust names of sheet if need be.

now you have 1 workbook with all the data safely inside. Make sure that nothing is trying to link to the other file formula wise, (ie: no =[file1.xls]Sheet2!A3+....

See if that helps you out, it should not interfere with any of your formulas in the destination workbook.

Regards,
jc

 

[Magriza]

It would depend on if the agents were static. If they were then you could create a fixed link. If not then you could use an INDEX/MATCH or VLOOKUP function to pull the correct data by agent (assuming each agent has a unique ID). You might need to check for a match in each workbook first.

Alternatively, you could reference the entire section of each worksheet. If you check the size of the area (10 rows x 4 columns for example) in the source workbook, then in the target you could select the same size area, press = and then select the source area and confirm with Ctrl+Shift+Enter. This has the benefit of not being able to tamper with individual cells of the array.

 

[degett]

Transferring the information is pretty simple.

if using excel 2007,
have both workbooks open.
hold shift and click each tab in 1 workbook, they should be selected now.

Right click the tab and select copy/move to

check the box that you want to create copies, and from the drop down box, change the destination to the other workbook.

>ok

Check out what happened. Any duplicate named sheets will be ___(2), adjust names of sheet if need be.

now you have 1 workbook with all the data safely inside. Make sure that nothing is trying to link to the other file formula wise, (ie: no =[file1.xls]Sheet2!A3+....

See if that helps you out, it should not interfere with any of your formulas in the destination workbook.

Regards,
jc

I understand what your trying to explain but its not really about copying and pasting, is there not a formula i could use? like ex: =[Test1 Stats]Sheet1!A2+[Test2 Stats]Sheet1!A2 ?

like does the information have to be on the actual workbook itself? or can i link to another workbook to get the stats? because Test1 stats and Test2 stats are the names of two different excel workbooks

Thanks so much for the help guys!

 

[jc.021286]

Yes,
You will need to update links and ensure that pathing is correct.
Simply building onto your previous workbook,
have both workbooks open.
Edit a formula and add "+" at the end of it and move to the other workbook, it should update the formula accordingly to pull from that workbook.

Regards,
jc

 

[degett]

HEYY! i finally got it!! i used this formula and its working like a charm!

=TRANSPOSE('[stats1.xlsx]Summary '!$O$4+'[stats2.xlsx]Agent break down'!$C$4)


THANKS FOR ALL UR HELP GUYS!!