| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return a Specific Value
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a Specific Value
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(B225,B$1," ")&IF(C225,C$1," ")&IF(D225,D$1," ")&IF(E225,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 --- "Tami" wrote: I need a formula 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a Specific Value
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a Specific Value
=SUBSTITUTE(TRIM(IF(B225,"1 ","")&IF(C225,"2 ","")&IF(D225,"3
","")&IF(E225,4,""))," ",",") "Tami" wrote: 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a Specific Value
thanks...that worked:-)
"Teethless mama" wrote: =SUBSTITUTE(TRIM(IF(B225,"1 ","")&IF(C225,"2 ","")&IF(D225,"3 ","")&IF(E225,4,""))," ",",") "Tami" wrote: 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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a Specific Value
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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a Specific Value
And it could be incorporated into a worksheet_change event to be completely
automatic. -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a Specific Value
You're Welcome!
"Tami" wrote: thanks...that worked:-) "Teethless mama" wrote: =SUBSTITUTE(TRIM(IF(B225,"1 ","")&IF(C225,"2 ","")&IF(D225,"3 ","")&IF(E225,4,""))," ",",") "Tami" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Return a Specific Value
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 "Don Guillett" wrote: And it could be incorporated into a worksheet_change event to be completely automatic. -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return the filtered value into a specific cell | Excel Worksheet Functions | |||
| A mysterious if function to return a specific balance? | Excel Worksheet Functions | |||
| Return Values in a specific column | Excel Discussion (Misc queries) | |||
| Return Maximum value for Specific Month(s) | Excel Worksheet Functions | |||
| What function do I use to return a certain value for specific text | Excel Discussion (Misc queries) | |||
Linear Mode
