Search results

Well, I'll hope I can pay up for this hectare space some day, but I'll go with this mega formula...

Can you confirm that only the asterisk (*) will be the extra character in your lookup table? Will everything else be exactly the same?

Sub SMC() 'Create an Outlook object and new mail message Set oApp = GetObject(,"Outlook.Application")'You must ensure that Outlook is open and logged in Set oMail = oApp.CreateItem(0) 'Set mail attributes (uncomment lines to enter attributes) ' In this example, only the attachment is being...

When you say exact matching, does that mean you will have "Y-lease" and "Y*-lease" both listed in the sub-table? Can you post a workbook, with the expected output.

Not sure why you want to automatically open the PDF and go to outlook. Are you saying you want to 'attach' the PDF?

What is your lookup table? If all the products are unique in the sub-table (look-up table), which is in $C$2:$D$12, you can use C15=VLOOKUP($B15,$C$2:$D$12,2,0)

Thanks Narayan. If that's the case, then I'd go with your and Hui's suggestion/thought.

Did you try this? Not sure if that's what you were looking for, but just in case.

Congratulations Hui.

You can do it this way. And keep on inserting rows (up to 100 rows in my example below, but you can increase that) =SUM(OFFSET(E7,,,MATCH("Scores",A7:INDIRECT("A106"),),))

Can't you use those individual functions in their corresponding rows, and the use a sum function like this? =SUM(E7:E9) Maybe I'm not understanding your question right.

'Callback for galCalendar onAction Sub galleryOnAction(control As IRibbonControl, id As String, index As Integer) 'This is where we pass the value of the selected date, on to the sheet 'Of course this will only pass value to the active cell. So if you've selected a range of cells, still...

Hi All Wasn't able to spend time on the forum over the last couple of weeks as I was busy with client visits at work, and had to focus attention there at work. Anyway, a good man, Neil Holder, an Excel enthusiast like most of us, has given a valuable insight about the Ribbon Date-picker I...

Here's one way of doing it using array formulas. There must be a better formula, but just a bit lazy here :D =SUM(INDIRECT("A"&MATCH(12,COUNTIF(INDIRECT("A"&ROW($1:31)&":A"&ROW(A31)),"<>"),)&":A"&ROW(A31))) if you are on the 32nd row

Just to remind you, that the above code will only work if you've pasted the entire code in the respective sheet module. It is an event triggered procedure that is based on the class within which it is written (read supposed to be written), in your case, the sheet module.

A simple searching would have given you enough ideas. Did you try http://chandoo.org/wp/2010/02/19/excel-consolidate-data/ http://www.excelfox.com/forum/search.php?searchid=164615 also will give you a few methods, more specifically...

Abhi, Application.Volatile--the default parameter is True--makes the function volatile, ie, whenever there is a change in any cell in the sheet, the formula will automatically recalculate itself. On the other hand, if you do not use Application.Volatile in a user defined function such as the...

Here's another shorter version Function SMC(Optional p1 As String, Optional p2 As String) As Long Application.Volatile True With ThisWorkbook If p1 = "" Then p1 = .Sheets(1).Name If p2 = "" Then p2 = .Sheets(.Sheets.Count).Name SMC = Abs(.Sheets(p2).Index -...

bobhc, format painter will bring in the conditional format also. OP didnt want that, but only the display color.

Cross-posted here http://www.ozgrid.com/forum/showthread.php?t=60398

By the way, please used code tags while posting codes. It enhances readability.

When you add a new style, Excel by default takes some values. The default value for Bold is False. So if you do not specify it, Excel will assume that you do not want to alter the default Bold (or Italics) features.

Hehe.... sometimes it looks ever so sophisticated for a moment, only to then turn up as quite stupid. Yes, much more elegant solution that.

I doubt that simply copying the range over would work as you expect. The following link should work for displaying the interior color (but not other formats like font being bold etc). Be adviced that this will only work for Excel 2010 and above...

Many ways to do that. Here's one of them Sub SMC() Const strSheetStart As String = "Sheet3" Const strSheetEnd As String = "Sheet2" Dim sht As Object For Each sht In Sheets If lng Then lng = lng + 1 End If If sht.Name = strSheetStart Then...