Return a Specific Value

[Tami]

I need a forumula to do the last column: tell me what weeks the units are
arrivng (e.g. 1,2,4) but the catch is that the units need to be greater thatn
25 in any given week (i don't want the formula to state the week # if its an
immaterial week.

wk1 wk2 wk3 wk4 weeks
Style1 200 300 0 100 1,2,4
Style2 0 0 100 0 3
Style 3 0 200 0 150 2,4

 

[Max]

Assuming the col headers in B1:E1 are amended
to be just numbers, eg: "1" instead of "Wk1" (drop the "Wk"),
you could then place this expression in F2:
=SUBSTITUTE(TRIM(IF(B2>25,B$1," ")&IF(C2>25,C$1," ")&IF(D2>25,D$1,"
")&IF(E2>25,E$1," "))," ",",")
and copy F2 down to return exactly the results that you seek

Success? Celebrate it, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Don Guillett]

How about a nice macro. Change your column number to suit the column with
styles

Sub whichweeks()
Dim mc, i, j As Long
Dim ms As String

mc = 10 '"k"

For i = 2 To 4 'Cells(Rows.Count, mc).End(xlUp).Row
ms = ""
For j = 1 To 4
If Cells(i, j + mc) > 25 Then ms = ms & j & ","
Next j
Cells(i, mc + 5) = (Left(ms, Len(ms) - 1))
Next i
'MsgBox ms
End Sub

 

[Teethless mama]

=SUBSTITUTE(TRIM(IF(B2>25,"1 ","")&IF(C2>25,"2 ","")&IF(D2>25,"3
","")&IF(E2>25,4,""))," ",",")

 

[Tami]

thanks...that worked

=SUBSTITUTE(TRIM(IF(B2>25,"1 ","")&IF(C2>25,"2 ","")&IF(D2>25,"3
","")&IF(E2>25,4,""))," ",",")

 

[Tami]

Whoa...a little to advanced for me. But how would that work?...you would run
the macro every time to get it to calc? i'm just not familiar with using
macros as "formulas"...

 

[Don Guillett]

Each time you fire the macro it would re-do for all the styles in the
columns.
Instead of "with that you get fries", here "with that you get commas"

 

[Don Guillett]

And it could be incorporated into a worksheet_change event to be completely
automatic.

 

[Teethless mama]

You're Welcome!

thanks...that worked

 

[Tami]

hi...can you look at my question under "sum until hit blank"...i'm getting
#n/a and don't know how to fix...thx