Dear Roger,
thank you so much for the offset formula! A wee error threw me into
confusion at first (should be column()+5 and not 4 towards the end) but I
learnt lots and understood the formula much better during the troubleshooting.


"Roger Govier" wrote:

Hi Melissa

A simple construct like Max has shown would pull data from Table1 and
Table 2 to a new 4th Table.
Your values for Table 3 could then be obtained either using Max's array
formulae or, the Offset solution I posted.

If you really do want to use Sheet1 and Sheet2 then the horrible formula
is

=(IF(ISERROR(SUM(OFFSET(Sheet1!B2,0,6,1,8-COLUMN()))),0
,SUM(OFFSET(Sheet1!B2,0,6,1,8-COLUMN())))+
SUM(OFFSET(Sheet2!$B2,0,0,1,COLUMN()+4)))/12

as we need to take a decreasing number of columns from Sheet1 and an
increasing number of columns from Sheet2

--
Regards

Roger Govier



Melissa wrote:
Thanks to Ken and Roger for your suggestions! I know having all in
one table would really make life easier but my tables 1 and 2 are
actually in 2 excel files. Is there no other way to have table 3
read from these 2?

Ken,
if I use your AVERAGE formula, I would get the wrong answer from Feb
Year 2 onwards as there will be a "gap" between Dec Yr 1($B$13) and
Jan Yr 2 (E2).


"Ken Johnson" wrote:

Hi Melissa,
If Table 1 is Jan - Dec in A2:A13, values in B2:B13,
Table 2 is Jan - Dec in D2:D13, values in E2:E13,
Table 3 (For averages) is Jan - Dec in G2:G13 then average values, as
described, can be generated by typing =AVERAGE(B8:$B$13,E2:E7) into
H2 then filling down to H8.

Does this help or confuse?