Repost of 12 month problem.

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

2/15/2004 (this is the search date entry.) lets say A1


This is the Months backed off from the above entry date
month, 12 total. I need this to be automatic so any date
entered in A1 will update the items below.

Aug-03 Sept-03 Oct-03 Nov-03 Dec-03 Jan-04 Feb-04
? ? ? ? ? ? ?

The ? are the number of entrys (on another worksheet)
made during the months that have been backed off of the
search date. I would hope to use the backed off months to
search the other worksheet and sum the amount of enrties
for each of the past months.
Is this possible.
 
Alex
I tried this layout:

15/02/04 in A1 sheet4. Original data in Sheet5. Column A
to L

Formula in A3 =EDATE(A1,-6)
Formula in B3 and dragged across =EDATE(A3,1)
Table called data with cell references
1 SHEET5!A:A
2 SHEET5!B:B
3 SHEET5!C:C
4 SHEET5!D:D
5 SHEET5!E:E
6 SHEET5!F:F
7 SHEET5!G:G
8 SHEET5!H:H
9 SHEET5!I:I
10 SHEET5!J;J
11 SHEET5!K:K
12 SHEET5!L:L
Formula to find number of entries in column in A4 and
copied across.

=COUNTA(INDIRECT(VLOOKUP(MONTH(C3),Data,2)))-1

Regards
Peter
 
Alex

Counta is the column for the month in sheet2 so check the
table is correct first.
if the Month date using EDATE is in C3 then the formula
goes in C4
=COUNTA(INDIRECT(VLOOKUP(MONTH(C3),Data,2)))-1

Counta count the cells with an entry in them. The minus
one is to subtract A1 which presumably, has the month
heading.

Hope this is clear.

Regards
Peter
 
Back
Top