Formula needed.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
Duke,

this only works once. As soon as D2 is filled using the same formula =C2+1m
C2 blanks since the max of D:D is greater then B2+1.
 
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$1:D1,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
 
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
 
Beertje said:
.. 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?
Click to expand...

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
 
Thanks. It works.

Max said:
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
Click to expand...
 
Back
Top