Replies

That didn’t work… here is all the code, in case I should have showed more than what I originally did…

Sub SendEmail()

Dim noSession As Object, noDatabase As Object, noDocument As Object
Dim obAttachment As Object, EmbedObject As Object

Dim stSubject As Variant, stAttachment As String

Dim vaRecipient As Variant, vaMsg As Variant

Const EMBED_ATTACHMENT As Long = 1454

‘********************************************************

stPath As String = “K:GroupsADPPhantom StockReports2012Reportsemail statements” & Sheet4.Range(“C2”)
Dim stFileName As String
Dim FileName As String
FileName = ActiveSheet.Name

stAttachment = stPath & “” & FileName & “.pdf”

‘Initiate the Lotus Notes COM’s Objects.
Set noSession = CreateObject(“Notes.NotesSession”)
Set noDatabase = noSession.GETDATABASE(“”, “”)

‘If Lotus Notes is not open then open the mail-part of it.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

‘Create the e-mail and the attachment.
Set noDocument = noDatabase.CreateDocument
Set obAttachment = noDocument.CreateRichTextItem(“stAttachment”)

Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, “”, stAttachment)

vaRecipient = Range(“A3”)

‘Add values to the created e-mail main properties.

With noDocument
.Form = “Memo”
.SendTo = vaRecipient
.Subject = Sheet4.Range(“A2″) & ” Phantom Stock Statement”
.Body = “Attached is your ” & Sheet4.Range(“A2″) & ” Phantom Stock Statement.”
.SaveMessageOnSend = True
End With

‘Send the e-mail.
With noDocument
.PostedDate = Now()
.Send 0, vaRecipient
End With

‘Release objects from the memory.
Set EmbedObject = Nothing
Set obAttachment = Nothing
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

‘Activate Excel for the user.
AppActivate “Microsoft Excel”
MsgBox “The e-mail has successfully been created and distributed.”, vbInformation
End Sub

This is what worked:

=SUMIFS(H:H,D:D,D2,G:G,CONCATENATE(“<=",G2))

I've reattached the example file – I've saved it in Excel 2007.

Thanks again Retired Geek & Steve!! I really appreciate all your help!

Lana

[TABLE="width: 64"]
[TR]
[TD][/TD]
[/TR]
[/TABLE]

Bingo!!! Thanks so much Retired Geek… you rock. This is exactly what I was looking for, and could have used years ago. This will be a new formula that I’ll be sharing with our accounting department on Monday! I’ve been doing a work around to get what I wanted it to do for years (and it involved a lot of columns and formulas). For some reason, I decided yesterday that there had to be an EASIER way… I’ve lost sleep on this one.

Thanks again!
Lana

Hi Steve,

The concatenated column (column P) is correct. The sumif doesn’t work if the periods are not sorted in chronological order. I’ve attached a new example.

Is there a formula that will do the following:

SUMIF(D:D,D2,H:H) is how I always use the SUMIF formula, but is it possible to modify this formula to do the SUMIF(D:D,D2,H:H), if G:G is <= D2???

Thanks!!
Lana

Hi Retired Geek,

Thank you for your response. I know it sounds silly that I wouldn’t sort the database. It’s a huge database that I extract from our AS400 by using msquery. I have other columns with formulas next to the msquery that will autofill up and down as the queried database size changes. The sort for these very large database would need to be on the concatenated column, which is NOT formulated inside the msquery, which means the sort would need to be done in Excel. I tried to add the concatenation inside of the msquery so when the data is extracted it would already be sorted the way I want it (and the way you suggested), but it doesn’t work – or I don’t know how to make it work. In a nutshell, I’m trying to avoid having to do any “massaging” of the database “after” it’s been downloaded into Excel. Also, I think if a “sort” action is forgotten, or a macro to “sort” is screwed up and it the “sort” didn’t happen, then the formula is quite vulnerable, and would open the door to producing an inaccurate calculation. I’m trying to maintain control of all the variables, or “what-if” scenarios. I thought there might be a formula that I’m not aware of that would do it. I’m still hopeful there is a formula, or a combination of formula’s that I don’t use regularly that would acheive what I’m trying to accomplish. As usual, any suggestions are much appreciated!

Thanks,
Lana

Hi again!

I just realized that it doesn’t work if there is a period missing from the database. The database is a trial balance, so some will not have all 13 periods, as not all periods will have activity in them. Also, the data may not be sorted in the correct period sequence order (for example, MARCH may be before JANUARY). Can this formula be changed to accomodate these types of variables?

Thanks!!
Lana

Hi Steve,

That was awesome! Wow, I’ve used the SUMIF formula forever… I had know idea it could do that. Why I didn’t ask this question before is beyond me. Anyway, I’m wondering if you could explain the logic behind it for me. I don’t understand how it knows to add OPN+JAN+MAR to get MAR YTD?

Thanks again Steve!!
Lana

Of course! Rounding… now I feel really dumb. I round everything else in my worksheets, but I falied to do so in this table! Thanks so much for opening my eyes tfspry! I must have been sleeping!
Lana

The formulas are still part of the table… when I click on a cell with a formula, the “Table Tools” tab DOES appear. In addition, if I pick the name of the query in the upper left hand corner of the screen (it’s call “Table_ExternalData_1”), it highlights the entire msquery as well as the cells with the formulas. In addition, the formatting of the table is also formatting the cells with the formulas. Also, the last row (row 65 in this example), has a tick mark in the bottom right hand corner. For example, the msquery is in A1:C65. Formulas should be in D2:E65, however they are only showing up in D2:E50. D51:E64 are blank, and D65:E65 actually have the formulas them. And cell E65 in particular has a tick mark in the bottom right hand corner of the cell… presumably to indicate it’s the last row & column of the table???? Is there a way to “reset” this fill down function… maybe turn it back on?
Thanks!
Lana

I finally got it to work… I renamed the picture to “object 6”, then I had to change the code from:

ActiveSheet.Shapes(“Object 6”).Select
Selection.Copy

to:

ActiveSheet.Shapes(“Object 6”).Copy

Then it worked.

Thanks!
Lana

Problem solved!!! Thanks so much Hans!! It’s as simple as pressing Ctrl + 6 to turn off the display objects function.
Thanks again!
Lana

I put the line into VBA’s immediate window and pressed enter and it worked (no error message) when I left it as a “1”, but then got an error message when I changed the cache to a “2”…. because I didn’t get an error message with the “1”, I’m thinking he’s on to something?!?!?!

Do I have to run a macro to change this cache index, or do I somehow change this cache index in some type of “properties” area? I couldn’t see the cache anywhere, so I must be looking in the wrong place.
Thanks!
Lana

Hi Rory,

Thanks for the info…. I had already changed the data source, and this indeed did “unlink” the pivot tables, however when I changed the data source back to the original, the link remained. I thought I could “trick” the pivot table to “unlink” by doing this… guess not!!!

Thanks for your reponse Rory!
Lana

I was afraid that would be the answer… I was trying to avoid making one from scratch and thought there might be a way to “unlink” it without starting over. Oh well.
Thanks for answering so quickly Hans!
Lana