Transfer data between sheets

girapas

Board Regular
Joined
Apr 20, 2004
Messages
150
In Sheet1 I have some data, by month, for the 12 months and for many years.
That I want is to sum data of items a1-a5 for each of east-west-north-south and transfer it as shown in Sheet2. What is the code to do it without enter manually the formulas in each row?
<html><head><title>Excel Jeanie HTML</title></head><body><span style=" font-weight:bold;"></span><br /><b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:77px;" /><col style="width:63px;" /><col style="width:63px;" /><col style="width:63px;" /><col style="width:63px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:9pt; text-align:center; ">JANUARY</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; text-align:center; ">east</td><td style="font-size:9pt; text-align:center; ">west</td><td style="font-size:9pt; text-align:center; ">north</td><td style="font-size:9pt; text-align:center; ">south</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:9pt; text-align:center; ">a1</td><td style="font-size:9pt; text-align:right; ">11291</td><td style="font-size:9pt; text-align:right; ">13651</td><td style="font-size:9pt; text-align:right; ">14651</td><td style="font-size:9pt; text-align:right; ">11105</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:9pt; text-align:center; ">a2</td><td style="font-size:9pt; text-align:right; ">14987</td><td style="font-size:9pt; text-align:right; ">9378</td><td style="font-size:9pt; text-align:right; ">13614</td><td style="font-size:9pt; text-align:right; ">9178</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:9pt; text-align:center; ">a3</td><td style="font-size:9pt; text-align:right; ">9107</td><td style="font-size:9pt; text-align:right; ">10125</td><td style="font-size:9pt; text-align:right; ">12853</td><td style="font-size:9pt; text-align:right; ">11100</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:9pt; text-align:center; ">a4</td><td style="font-size:9pt; text-align:right; ">10609</td><td style="font-size:9pt; text-align:right; ">12174</td><td style="font-size:9pt; text-align:right; ">11112</td><td style="font-size:9pt; text-align:right; ">11068</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:9pt; text-align:center; ">a5</td><td style="font-size:9pt; text-align:right; ">14432</td><td style="font-size:9pt; text-align:right; ">10452</td><td style="font-size:9pt; text-align:right; ">12494</td><td style="font-size:9pt; text-align:right; ">13006</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:9pt; text-align:center; ">FEBRUARY</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td style="font-size:9pt; text-align:center; ">east</td><td style="font-size:9pt; text-align:center; ">west</td><td style="font-size:9pt; text-align:center; ">north</td><td style="font-size:9pt; text-align:center; ">south</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:9pt; text-align:center; ">a1</td><td style="font-size:9pt; text-align:right; ">9845</td><td style="font-size:9pt; text-align:right; ">14637</td><td style="font-size:9pt; text-align:right; ">10377</td><td style="font-size:9pt; text-align:right; ">12820</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:9pt; text-align:center; ">a2</td><td style="font-size:9pt; text-align:right; ">10171</td><td style="font-size:9pt; text-align:right; ">11430</td><td style="font-size:9pt; text-align:right; ">9338</td><td style="font-size:9pt; text-align:right; ">13407</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:9pt; text-align:center; ">a3</td><td style="font-size:9pt; text-align:right; ">11403</td><td style="font-size:9pt; text-align:right; ">14997</td><td style="font-size:9pt; text-align:right; ">10691</td><td style="font-size:9pt; text-align:right; ">13455</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:9pt; text-align:center; ">a4</td><td style="font-size:9pt; text-align:right; ">14193</td><td style="font-size:9pt; text-align:right; ">12122</td><td style="font-size:9pt; text-align:right; ">12504</td><td style="font-size:9pt; text-align:right; ">13627</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:9pt; text-align:center; ">a5</td><td style="font-size:9pt; text-align:right; ">9767</td><td style="font-size:9pt; text-align:right; ">9460</td><td style="font-size:9pt; text-align:right; ">9161</td><td style="font-size:9pt; text-align:right; ">11481</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:9pt; text-align:center; ">MARCH</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td style="font-size:9pt; text-align:center; ">east</td><td style="font-size:9pt; text-align:center; ">west</td><td style="font-size:9pt; text-align:center; ">north</td><td style="font-size:9pt; text-align:center; ">south</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:9pt; text-align:center; ">a1</td><td style="font-size:9pt; text-align:right; ">14819</td><td style="font-size:9pt; text-align:right; ">10403</td><td style="font-size:9pt; text-align:right; ">12666</td><td style="font-size:9pt; text-align:right; ">14883</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:9pt; text-align:center; ">a2</td><td style="font-size:9pt; text-align:right; ">12503</td><td style="font-size:9pt; text-align:right; ">11506</td><td style="font-size:9pt; text-align:right; ">9812</td><td style="font-size:9pt; text-align:right; ">11877</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:9pt; text-align:center; ">a3</td><td style="font-size:9pt; text-align:right; ">10169</td><td style="font-size:9pt; text-align:right; ">9522</td><td style="font-size:9pt; text-align:right; ">10379</td><td style="font-size:9pt; text-align:right; ">10371</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-size:9pt; text-align:center; ">a4</td><td style="font-size:9pt; text-align:right; ">13303</td><td style="font-size:9pt; text-align:right; ">12717</td><td style="font-size:9pt; text-align:right; ">13131</td><td style="font-size:9pt; text-align:right; ">13565</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-size:9pt; text-align:center; ">a5</td><td style="font-size:9pt; text-align:right; ">12574</td><td style="font-size:9pt; text-align:right; ">13798</td><td style="font-size:9pt; text-align:right; ">14338</td><td style="font-size:9pt; text-align:right; ">11888</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >28</td><td colspan="5" style="color:#ff0000; font-weight:bold; font-size:9pt; text-align:left; ">  it's continued for the rest months of the year</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td >.................</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >.................</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><br /><b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:48px;" /><col style="width:81px;" /><col style="width:77px;" /><col style="width:77px;" /><col style="width:77px;" /><col style="width:77px;" /><col style="width:77px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="font-size:9pt; "> </td><td style="font-size:9pt; text-align:center; ">a5</td><td style="font-size:9pt; text-align:center; ">a4</td><td style="font-size:9pt; text-align:center; ">a3</td><td style="font-size:9pt; text-align:center; ">a2</td><td style="font-size:9pt; text-align:center; ">a1</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:9pt; text-align:right; ">2008</td><td style="font-size:9pt; ">JANUARY</td><td > </td><td style="color:#ff0000; font-size:9pt; "> </td><td style="color:#ff0000; font-size:9pt; "> </td><td style="color:#ff0000; font-size:9pt; "> </td><td style="color:#ff0000; font-size:9pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">FEBRUARY</td><td colspan="5" style="color:#ff0000; font-size:9pt; text-align:left; border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#ffffff; "> for each item (a1-a5) I want the sum of </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">MARCH</td><td colspan="5" style="color:#ff0000; text-align:left; border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#ffffff; "> east+west+north+south for</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">APRIL</td><td colspan="5" style="color:#ff0000; font-size:9pt; text-align:left; border-bottom-style:solid; border-bottom-width:1px; border-bottom-color:#ffffff; "> the equivalent month</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">MAY</td><td colspan="5" style="color:#ff0000; font-size:9pt; text-align:left; "> (the items a1-a5 is here in reverse order, starting from a5)</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">JUNE</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">JULY</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">AUGUST</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">SEPTEMBER</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">OCTOBER</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">NOVEMBER</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">DECEMBER</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:9pt; text-align:right; ">2009</td><td style="font-size:9pt; ">JANUARY</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">FEBRUARY</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">MARCH</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">APRIL</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">MAY</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">JUNE</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">JULY</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">AUGUST</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">SEPTEMBER</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">OCTOBER</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">NOVEMBER</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="font-size:9pt; "> </td><td style="font-size:9pt; ">DECEMBER</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="font-size:9pt; text-align:right; ">2010</td><td style="font-size:9pt; ">...................</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >27</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4


</body></html>
 
To be more clear:
In sheet2 and for each a1-a5 I want the sum of east,west,north,south.
In sheet1 there are 2 blank rows after the end of each month.
 
Upvote 0
Hi
If you have only one year per sheet try:
=SUM(OFFSET(INDEX(Sheet1!$A$1:$A$100,MATCH($B2,Sheet1!$A$1:$A$100,0)),VALUE(RIGHT(H$2,1))+1,1,,4))
 
Upvote 0
Thanks for your reply. The formula results error (#VALUE!). I don't understand the last part of it (VALUE(RIGHT(H$2;1))+1;1;;4)) especially H$2.
 
Upvote 0
Example:

Code:
https://rapidshare.com/files/1815371860/look.xlsx
Sorry for trouble I have used my own leyout first time.
In attached example everthing is as on your spreadsheet
 
Upvote 0

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
Back
Top