# Posts by CurtFunk

• ## Can't make dependant combo boxes to work

Re: Can't make dependant combo boxes to work

Hi,

You need to set the "RowSource" property for each combo box. So set the property to the range of data you want listed in the combo box. For Categora, that would be "A2:A7".

Hope that helps,
CF

• ## Using multiple functions in SUM functions

Re: Using multiple functions in SUM functions

Hi Newman11

I think the problem was the number of brackets. I modified the formula to read as follows:

=((SUM(100-A1)/100)*A3)/A2

It gave a result of 1.823359.

Now technically, you don't need the SUM function in this example. The SUM function is use to add numbers in a range. So your formula could also have been:

=(((100-A1)/100)*A3)/A2

Remember that the number of open "(" should equal the number of closed ")".

Hope that helps,

CF

• ## Cell value based on another cell in different Sheet

Re: Cell value based on another cell in different Sheet

Hi Ryan

Check out the attached spreadsheet. I used the SUMIF function to total up all the values in the July-10 spreadsheet that match the subcategory in column B.

Hope that helps
CF

• ## Lookup Codes in Table

Re: How do display an item automatically when user keys in a code which matches the l

You would use the VLOOKUP function. In your sample spreadsheet, place the following formula into the cell you indicated:

=VLOOKUP(J8,E9:F15,2)

That should do it ...
CF

• ## compare and count similar cells

Re: compare and count similar cells

I didn't write any code but here is a suggestion. Take a look at the "like" operator. It allows you to look for partial matches between two strings. You can also use wildcards.

My thought would be to take the left of a string up to the first blank character and then use the "like" operator to see if that group of characters exists within the other string.

Proposed modification to your code ...

Code
``````comp = Left((Cells(w, 1)), InStr(1, Cells(w, 1), " ")) & "*"
Comp1 = Cells(w + 1)
If Not (Comp1 [COLOR=red]Like[/COLOR] comp) Then
Sheets("Consolidation").Cells(y, 1) = Left((Cells(w, 1)), InStr(1, Cells(w, 1), " "))
Sheets("Consolidation").Cells(y, 2) = subtot
subtot = Cells(w + 1, 3)
y = y + 1
Else
subtot = subtot + Cells(w + 1, 3)
End I``````

This will also solve your second problem since the value you place in your cell will be the full name (not just the first 6 characters).

Note this gets trickier if there is no space after the name.

Hope that helps,
CF

• ## Sum of nonstrikethrough numbers

Re: Sum of nonstrikethrough numbers

Aswani,

Upon reflection my original code would not work. I have attached a sample spreadsheet with a quick (and dirty) macro that will illustrate what I mean. Essentially, convert the value to a string , then format the strikethrough font and then right alighn it since text defaults to left align.

The code in the macro looks like this:

Run the macro1 in the spreadsheet and watch cell E14. You will see the number appear, with strike through, but the SUM does not change (your original quandry).

Hope that helps,
CF

• ## Sum of nonstrikethrough numbers

Re: Sum of nonstrikethrough numbers

One option might be to have your macro, when it creates the value in K18, convert the number to a string value. A SUM command somewhere else in the spreadsheet will ignore the string characters in its summation. Since it appears you are already formatting the cell with strikethrough format, you can do the conversion there - make sure you recreate any decimal and comma positioning using the "Format" function. An example:

Code
``myStringVlu = Format(Str(myNumVlu), "#,00.00")``

CF

• ## Using Excel 2007 to Prepare an Email in Outlook 2007 in a customized format.

Re: Using Excel 2007 to Prepare an Email in Outlook 2007 in a customized format.

A slightly different approach might be to use MS-Word and do a mail merge. You would use the excel sheet as your data source. You would have alot more power in formatting. I believe that you can do conditional formatting as well.

Just a thought ...
CF

• ## How to remove special character in cell before comparison?

Re: How to remove special character in cell before comparison?

I have luck with the following formula to clean out the non-printable characters:

=TRIM(SUBSTITUTE(E4,CHAR(160),CHAR(32)))

You would probably need a helper column to contain the cleaned up string against which you could do your comparison.

CF

• ## Macro for Opening workbooks

Re: Macro for Opening workbooks

Try looking at "Saving Workspace" on this web site http://www.ozgrid.com/Excel/save-as-workspace.htm I think it will do what you want without the need to develop and code or formula.

CF

• ## Extract three sets of numbers from one cell

Re: Extract three sets of numbers from one cell

Quote from 05125668;515341

CF,
Thanks again for your help. If I have any other further questions then I will let you know.
05125668

• ## Lookup from text string

Re: Lookup from text string

That is a very elegent solution Mike. I learn something every day.!

CF

• ## Lookup from text string

Re: Lookup from text string

Stamron

I think the easiest way is to use a user-defined-function (UDF). First I created a second sheet in your sample and put a table containing the keywords that you want to find (this way you can add more keywords). The range of keywords has be assigned a range name of "Keywords". The table that contains both the keywords and results has a range name assigned of "Categories". Next I created a UDF called "Find_Category" to which you pass the log text. The function then searches for each of the keywords located in the keywords range and if successful it returns the found keyword in the table. You burry that in a VLOOKUP and poof you have the "result". Since the search tables has to be sorted for the lookups to work, the tricky part was finding the first keyword match - you can read the code for that solution.

I have attached the resulting spreadsheet (note that it has an XLSM - Macro Enabled spreadsheet - format).
Hope that helps - CF

PS. If you have VBA experience then the following is the code that is in the function:

• ## Extract three sets of numbers from one cell

Re: Extract three sets of numbers from one cell

Hi Dan

Given the complexity of your data extract requirements I think that using an excel formula will exceedingly difficult (particularly if you want to avoid going blind reviewing the syntax). I think this is a good example for a user-defined-function (UDF). I am not sure your level of experience with UDF or visual basic. I developed a UDF that will allow you to pass all the different variations of the values you are looking for (i.e. "BLACK", "BLACK:", "BLACK " or "BLACK -". The function will return the associated value. You can use this function for the other lookups (like TOTAL, COLOUR, B/W etc.).

To make life easier I created the UDF and included it in the attached sample file (that was your original post). It illustrates how to use the function.

NOTE: The function assumes that there is always at least a " " before the value we are extracting. Also, the search is case-sensitive. Also, the excel sheet now has an XLSM extension (macro enabled spreadsheet).

I hope this helps ... CF

PS In the event that you have experience with UDF, I have included the code.

Those reading this thread - don't laugh at the code - but here is the code for the UDF

• ## Testing 123

Re: Testing 123

• ## Testing 123

[VBA]
Function Extract_value(search_string As String, search_vlu As Variant, Optional search_vlu2 As Variant = "", _
Optional search_vlu3 As Variant = "", Optional search_vlu4 As Variant = "", Optional search_vlu5 As Variant = "", _
Optional search_vlu6 As Variant = "") As Variant

Dim Text_Pos As Integer
Dim First_blank, Second_Blank As Variant
Dim values(6, 1) As Variant

values(0, 1) = search_vlu
values(1, 1) = search_vlu2
values(2, 1) = search_vlu3
values(3, 1) = search_vlu4
values(4, 1) = search_vlu5
values(5, 1) = search_vlu6

' Make sure that the search values are sorted largest to smallest
SortColumn1 = 0
For i = LBound(values, 1) To UBound(values, 1) - 1
For j = LBound(values, 1) To UBound(values, 1) - 1
Condition1 = values(j, SortColumn1) > values(j + 1, SortColumn1)
If Condition1 Then
For y = LBound(values, 2) To UBound(values, 2)
t = values(j, y)
values(j, y) = values(j + 1, y)
values(j + 1, y) = t
Next y
End If
Next
Next

' First look for the primary search value

For i = LBound(values, 1) To UBound(values, 1)
Text_Pos = InStr(search_string, values(i, 1))
srch_str = values(i, 1)
If Text_Pos > 1 Then Exit For
Next

If Text_Pos > 1 Then
'
' We assume that the number will be following the first "blank" character
' and that number will end at the next blank (or end of string)
'
First_blank = InStr(Text_Pos + Len(srch_str) - 1, search_string, " ")
Second_Blank = InStr(First_blank + Len(srch_str), search_string, " ") - 1
If Second_Blank = -1 Then Second_Blank = Len(search_string) + 1
Extract_value = Mid(search_string, First_blank, Second_Blank - First_blank)
Else
Extract_value = "None"

End If

End Function

[/VBA]

• ## Return Name based on Rank with multiple criteria

Re: Return Name based on Rank with multiple criteria

I am thinking that a pivot table may be your answer. Alternatively you can use data filtering on your 2k items to narrow the list. Depending on the version of excel (i.e. 2007) you could use "Tables" that combined with filter can allow you to include totals.

Hope that helps.

• ## Extract three sets of numbers from one cell

Re: Extract three sets of numbers from one cell

I think I understand what you are trying to do. From your example, I put the "B = 6742 F = 3027" into cell A8. I then put the following formulas into cell B8 and C8 respectively.

B8: =MID(A8,FIND("=",A8,1)+2,FIND(" ",A8,FIND("=",A8,1)+2)-FIND("=",A8,1)-2)
C8: =MID(A8,FIND("=",A8,FIND("=",A8,1)+1)+1,LEN(A8)-FIND("=",A8,FIND("=",A8,1)+1)+1)

Simply copy the formulas for the 300+ rows that you have.

Essentially the first formula extracts the text between the first "=" and the space after the number. The second formula extracts the text after the second "=". Note that this formula assumes that you have a "= ' format in your source text.

I have attached as sample that also shows how to convert it to a number (that you can do math on).

Hope that helps.

• ## Perform look up if source is not blank and hide #N/A if no match found

Re: Perform look up if source is not blank and hide #N/A if no match found

I have also had luck with the following:

=IFERROR(VLOOKUP(A5,'Master Food List'!3:200,4,FALSE)," ")

The IFERROR shown above will put a blank (or anything within the quotes) into the cell only if there is an error looking up the value otherwise it will put the lookup results.

• ## Lookup using mutilple criteria

Re: Lookup using mutilple criteria

What I have done in the past is set up an index column within the data (you can always hide the column). The cells within the index columns would be concatenation of the cells that you want to search by. In your example - assume that the vegitable is in column A and the colour in column B. You would insert a column in front of Column A. The new formula would be =A1&B1.

Then in your VLOOKUP function you would use the concatenated values of what you are trying to find.

I have attached an example. Cell I4 contains the VLOOKUP example.

Hope that helps.