Formula needed.

[Guest]

My first sheet (called Data) contains following information:
Col A Col B Col C Col D
Part 1 5 Date =weeknum(C1) i.e. 46
Part 2 6 Date 46
Part 1 4 Date 46
Part 1 5 Date 47
This list is filled with more rows with the same week number.

On another sheet (called Summary) I try to summarize the data like following:
Col A Col B Col C Col D
46 47 Formula Formula

I need a formula for this row, which automatically fills in the next week
number if it shows up in my database (only column per week). I.e. as soon as
in column D of the Data sheet the next week number shows up (here 48) it is
automatically shown in Col C of my summary sheet etc.

Who can help me with this formula?

 

[Guest]

I'm assuming 47 is in B2, so in C2 use

=IF(MAX(Data!D)=B2+1,B2+1,"")

 

[Guest]

Duke,

this only works once. As soon as D2 is filled using the same formula =C2+1m
C2 blanks since the max of D is greater then B2+1.

 

[Max]

Another play to try ..

In sheet: Data
-------------
Assume data starts in row1 down, col C contains dates & col D houses
formulas filled down (in D1: =weeknum(C1)) progressively

Use an adjacent col, say col E?
Put in E1: =IF(C1="","",IF(COUNTIF($D$11,D1)>1,"",ROW()))
Copy down to say, E200, to cover the max expected range in col C

Then in sheet: Summary
-----------------------
Put in say, A2:

=IF(ISERROR(SMALL(Data!$E:$E,COLUMN(A1))),"",
INDEX(Data!$D:$D,
MATCH(SMALL(Data!$E:$E,COLUMN(A1)),Data!$E:$E,0)))

Copy A2 across 104 cols to say, CZ2,
to cover 2 years worth of week#'s

A2:CZ2 will be progressively filled
with the week# from "Data" as desired

 

[Guest]

Max,
one issue remains
week 46, 47 ,48, 49 no problem from left to right, but now when week 1 of
next year starts it is put at the left of week 46, so my sequence gets mixed
up.

how to solve this? Can we include the year?

Thanks

 

[Max]

.. but now when week 1 of next year starts
it is put at the left of week 46,
so my sequence gets mixed up.
how to solve this? Can we include the year?

Not sure what you meant by the above, but perhaps this suggested amendment
to the formulae in col D in Data to include the week# and the year in the
display (" ##-yyyy") would work out ok ?

In sheet: Data
------------
Put instead in D1, and copy down:
=TEXT(weeknum(C1),"00")&"-"&TEXT(C1,"yyyy")

(Col E's formulae unchanged)

Then in sheet: Summary
with row2's formulae unchanged
it'll appear in A2 across as:

46-2005, 47-2005, ... 53-2005, 01-2006

Here's a quick sample for the above construct:
http://cjoint.com/?lDjJTMYIis
Beertje_misc.xls

 

[Guest]

Thanks. It works.

Not sure what you meant by the above, but perhaps this suggested amendment
to the formulae in col D in Data to include the week# and the year in the
display (" ##-yyyy") would work out ok ?

In sheet: Data
------------
Put instead in D1, and copy down:
=TEXT(weeknum(C1),"00")&"-"&TEXT(C1,"yyyy")

(Col E's formulae unchanged)

Then in sheet: Summary
with row2's formulae unchanged
it'll appear in A2 across as:

46-2005, 47-2005, ... 53-2005, 01-2006

Here's a quick sample for the above construct:
http://cjoint.com/?lDjJTMYIis
Beertje_misc.xls

 

[Max]

Glad to hear that !