Posts by lawsonrw

    Finished it myself, please find the necessary code below:

    This code helps in creating PO numbers that are specific to the user's initials:

    I saved the following as a single module since its all the "worker bees":

    And calling it all with a single button:

    1. Sub CompletedPO() 
    2. Call PrintAndSave 
    3. Call ResetDropDowns 
    4. Call SaveAndClose 
    5. End Sub 

    And you can see it all run together in the attached file. I hope someone else finds this helpful. Thank you to the community of coders all over the world who left breadcumbs for me to follow ♡♡♡♡♡♡♡♡♡♡

    LOL, I mentioned I would continue to massage the file, I realized I didn't discuss how I'd like the output to appear. The updated attached file has TempTbl filled in, this is more or less how I'd like to receive the PDF.

    Some additional thoughts:

    The printArea will need to automatically expand. On a day to day basis I'm restocking no more than 5 or 6 items, but should it ever be longer than that the printArea shouldn't leave anything out.

    Everything above Row 28 is basically copied over from the Purchase Order Form Worksheet.

    I'd like the saved PDF file name to be the purchase order number, extracted from cell J11 of either the TempTbl worksheet or the Purchase Order Form worksheet.

    As mentioned in the original specs, once the PDF has been emailed off it resets TempTbl to be blank, and also resets Purchase Order Form dropdown to blank and wipes the textbox as well.


    I need help with finishing my excel file. The idea is (1) one of my employees goes into the file and (2) selects which items need to be restocked then (3) presses a button which (3a) copies the selected items to the TempTbl worksheet and (3b) automatically index(match) each item to the Fulfillment columns from InventoryTbl then (3c) alphabetically sorts this new table first by fulfillment then by item so that it may finally (4) print to PDF, email me the PDF, then wipe TempTbl clear. Once the email is sent, the form resets all dropdowns on the form to blank, saves the file, and notifies the user the email was sent with a thank you message, once they click "Ok" it closes Excel.

    *it will send the file from a microsoft outlook email account that's already logged into the Windows user.

    *i will ideally password protect and/or hide the Admin worksheet so that it is only accessible to an admin, the end user in this case should only interact with the form.

    *the file is hosted in a OneDrive folder and shared with local users on the PC. I imagine it might be logical to store the saved PDFs to a subfolder in the same shared folder as the file, lets call that Subfolder "PO Archive" for working purposes

    *I really only want the end user to be able to (a) manipulate the dropdowns, (b) add text to the text box under notes and and (c) submit the form. Ideally they won't see any of the formulas or behind-the-scenes machinations of how the file works (less room for them to mess it up) ... i know there is a way to hide formulas and lock cells, I can do this myself but just stating that I will probably continue to massage the file

    *I'm clearly not a professional at this, so if you see any opportunities to streamline this, please go for it!

    The file is attached.

    Thanks in advance for all your help!

    KjBox, wow!!!

    If I totally had my say, this would be the implementation. But my managers are .... change averse. They've been using this old t-account setup for more than a decade, they've all been here more than a decade, and they kinda go crazy when you try to change something. I really appreciate your efforts and I'd use it if I thought it had a chance of winning support from my bosses but from my 2 years of being here, I can say that is beyond unlikely.

    Some background for anyone who might even care:

    We are a large healthcare nonprofit that is more than 100 years old, as is common, we invest excess cash because keeping it in "cash" is a losing position when you consider inflation. Our old method was to open an individual account at our custodian bank for each investment. So think of it this way: Your 401(k) might have five mutual funds (for example). Thats 1 account with 5 investments. We used to open a separate account for each investment, so using our method, you would have 5 separate 401(k) accounts, each holding just a one investment! This got to be burdensome and expensive.

    So we're transitioning to a Master Trust structure wherein all the investments are held in a single account, this would move us back towards the 1 account with 5 funds in the above example. It used to be very easy to reconcile investment activity because each account only showed activity for that specific investment. But now we're going to have 66 investments in one account. For audit reasons, we need a "master table" that reasonably matches our custodian bank. But for us to track the individual actions of each investment, we still need to see activity from that perspective.

    I'm going to be creating "closing entries" on all 66 of the old t-accounts and kinda starting fresh with this new structure, but management is extremely touchy about being able to do things the way they've always done them ::insert eye roll::

    Again KjBox, thank you!!!

    Again, thank you so much Carim! Everything works, its very clean, and exactly what I was hoping to get to!!

    I forgot, what did you say your CashApp handle was?

    Last thing: I added an Investment C using the procedure I outlined earlier, after running the Index & Admin button, Investment C did populate on the Admin tab but it appeared "below" the table instead of "on the table" idk if that makes sense, but file is attached with those changes.

    Thanks Carim!

    First, lets not call it 'compensation,' and just a "tip." For real, drop you CashApp handle.

    It works amazingly!

    Last thought:

    There's an "Investments List" on the Master Table worksheet that hyperlinks to each respective fund-specific t-account. But these are the same as the Funds Table on the Admin worksheet. Would it be possible to just have the Funds Table on the Admin worksheet have the Names hyperlinked, cutting down on redundancy?

    [h=1][/h] Thanks Carim!

    Two questions:
    -I'd like to thank you by sending a CashApp payment so if you want to direct message it to me or share it here, look for a cash thank you from me!
    -Would it be smart to put the "Index Funds" button on the Admin tab and let it populate the names directly onto the table? The Indirect formula would then automatically pickup the 'Committed' and 'Callable' amounts?

    Ah yes, you're right! I saw that you did that in the file you sent back, just didn't make that change in the version where I added your VBA. Obvious version control issues on my end, lol, thanks!

    Hi Carim,

    That's a nifty bit of code! In the full version I created a table of contents that hyperlinks to the desired tab (there are 66 existing t-accounts, with more to come), which just made it easier to get to the correct tab to input accounting entries.

    But to answer your question, yes, it is simply a recap all the entries from the "child" t-accounts. With the headers filtering turned on, yes I will most likely have them sorted by date, but I might also want to sort by "transaction description" and gather up all the Capital Calls for a given period, for example. On the newly attached file (which includes your VBA), you can see I added an additional attribute column on the Master Table.

    Hi royUK!

    Thank you for the help! I do need to maintain the individual T-Accounts because that's basically what management wants, lol. But in all honesty it does help us more directly track unfunded commitment, paid-in capital, and the call as a % of commitment. This could be accomplished with a slick new pivot table and VBA and kinda have it setup as a form/report but I've tried implementations like that and it honestly just scares off management because they're so unfamiliar with those kinds of tools. So I've resolved to try to keep it "low tech" and more familiar for them, which is an entirely different set of challenges.

    Hello World!

    I'm not sure if this would fall under formulas or VBA, but here's my best attempt at explaining what is going on:

    My employer tracks investment cashflows using basic t-accounts. We are still going to input cashflows using independent t-accounts for each investment, but we now want all that data to automatically flow into a "master chart"

    If you image, Investment A pays a dividend of $100 on November 1... I'll input that on the T Account (which is a unique worksheet) for Investment A. Investment B pays a $25 dividend on November 4, the same procedure is triggered: I input that activity on a t-account for Investment B. If I print either worksheet (for investments a or b) it will only show data for THAT particular investment. But I also need a "Master Account" which automatically collects new lines from its "child tables" and copies them to the next available line on the Master Chart. The idea is to not be required to enter the same data twice, and thereby avoid human-error in doing so.

    Additionally, there will be a constant influx of new investments with their own t-charts so whatever the tool is, be it formula or VBA, needs to be relatively easily "reprogrammed" to look at new worksheets.

    Lastly, I *think* I want to accomplish this with a pivot table, which would allow me to slice and dice the data in the future on an ad hoc basis... but I don't know how to get the data from the individual t-accounts to flow into a table.

    Attached is a sample mock-up of what I'm trying to get to. Any help is much appreciated!

    what cells are the constants ?

    Hi Pike!

    StartingValue = K4 for Eddard Stark | L4 for Pycelle, etc.
    %Change references relative dates and names:

    ...lets say we're working with MSCI ACWI. The variable named myArray(1) already has a value set to 15%. The VBA then has to go to 'Table2' located on tab 'Where the Action Is', find the most recent date for MSCI ACWI which is 8/16/2019 and store the price from that date for the % change formula as "EACWIcv." It then needs to find the earliest date in MSCI ACWI for that year (2019)... which is 1/2/2019 and store the price from that date as "EACWIpv." It then needs to calculate the %Change which is: (1+(EACWIcv-EACWIpv)/EACWIpv)... in this case, EACWIcv = 71.18, EACWIpv = 64.139999 so the formula with the numerical values inserted would read (1+(71.18-64.139999)/64.139999) = 1.109759918 = %Change

    Inserting the product of %Change in the formula we would get CurrentValue = StartingValue * myArray(1) * 1.109759918 + StartingValue * myArray(2) * 1.109759918 + ..... StartingValue * myArray(n) * 1.109759918

    Which is why %Change can be stored as a variable instead of calculated for each iteration of myArray(n)

    Inserting numerical values for all the variables would read CurrentValue = 408,910,275 * .15 * 1.109759918 + 408,910,275 * .30 * 1.109759918 + 408,910,275 * .25 * 1.109759918 + 408,910,275 * .08 * 1.109759918 + 408,910,275 * .22 * 1.109759918, CurrentValue = 455,352,242


    If you take a look at the tab titled ''Outputs' you will see where this is being calculated in E27. For this particular method, Prior Period Close = StartingValue, so we see that represented as a simple cell reference. Below that we see the actual % change is is similar to the %Change formula above, but this is basically the YTD performance of the mixed portfolio, instead of individual indices. and we see a nominal change, so this is the dollar amount change, saying that with the existing asset allocation mixture, Eddard Stark has earned $46MM YTD...

    Right now we're only working with the one portfolio, EddardStark
    And for the one method: YTD

    But this needs to be repeated for the other 3 portfolios, and for the 2 other methods: Monthly and Weekly.. These two methods have to calculate the "PP Close" the same way they calculate the CP Close, but for different dates. I can provide more detail if you're interested.

    I know this is A LOT that I'm asking. I'm willing to pay because this will literally save me hours of frustration every week, as I'm expected to produce these numbers weekly (our statements, like most people, are monthly, and the higher ups want to get a sense of where we are on an intra-month basis). I'm just trying to put as much of it in VBA as possible because it will remove the human factor. Plus I want to be able to produce outputs on the fly, so with monthly (for example), I could pull a drop down and select July, or February, and everything calculates, instead of me scrolling, clicking, scrolling, etc... messing up somewhere, not catching it, getting yelled at. I thought I had a grasp on VBA until I attempted this.

    Please send me a private message and we can settle on price and payment method (cashapp?)

    I can attempt to answer a specific question about excel/vba but not solve an equation ,, you could try recording a macro to fill in the blanks or make a worksheet formula of the problem.
    Can you expnad and ask a specific question?

    Hi Pike!

    Now that the nonblanks have been populated into array variables, how would I insert that variable into the following equation?:

    CurrentValue = StartingValue * myArray(1) * PercentChange + StartingValue * myArray(2) * PercentChange + ..... StartingValue * myArray(n) * PercentChange

    For n iterations of the array?

    Thank you Pike!

    Can you help me get to the final equation:

    CurrentValue = StartingValue * myArray(1) * PercentChange + StartingValue * myArray(2) * PercentChange + ..... StartingValue * myArray(n) * PercentChange

    Where PercentChange = (1+(index(match(a) + index(match(b))/index(match(b))
    **index(match) will be done to pull prices for specific dates

    I think this gets me the array??? But how do I now store the cell values in the actual variables, as in:

    EAlloc1 = 5% | EAlloc2 = 15% | EAlloc3 = 40%... n

    Sub MVUpdate()
    Dim EndowSV As Integer
    Dim PenSV As Integer
    Dim OperSV As Integer
    Dim AllegSV As Integer

    ThisWorkbook.Sheets("Outputs").Range("K4").Value = EndowSV
    ThisWorkbook.Sheets("Outputs").Range("L4").Value = PenSV
    ThisWorkbook.Sheets("Outputs").Range("M4").Value = OperSV
    ThisWorkbook.Sheets("Outputs").Range("N4").Value = AllegSV

    Dim EndowR As Range, Er As Range, ErSel As Range

    Set EndowR = ThisWorkbook.Sheets("Outputs").Range("K5:K20")
    Set ErSel = Nothing

    For Each Er In EndowR
    If Er.Value <> "" Then
    If ErSel Is Nothing Then
    Set ErSel = Er
    Set ErSel = Union(ErSel, Er)
    End If
    End If
    Next Er
    If Not ErSel Is Nothing Then ErSel.Select

    MsgBox Selection.CountLarge

    Dim i, x As Integer
    x = ErSel.Count

    Dim EAlloc() As Integer
    ReDim EAlloc(1 To x) As Integer

    For i = 1 To x

    EAlloc(i) = i

    End Sub

    THEN how do I capture the names of the indices so I can start working on my index(match) to get to my ultimate calculation:

    CV = SV * EAlloc1 * EPerChange + SV * EAlloc2 * EPerChange ... SV * EAllocn * EPerChange

    Next I'm trying to select the nonblank cells in the allocations table and have this... so far it works in that it is successful in selecting the nonblank cells. If you run it, you will end up with each nonblank cell in the range "selected."

    What I want to do now is pass these selections on to variables:

    5% should = EdAllocation1
    15% should = EdAllocation2

    This specific feature is important because it preserves the ability to make "on the fly" changes to allocations without requiring a change in VBA.

    But my dilemma is 2-fold:

    Fold 1:

    Each portfolio (Eddard, Pycelle, Oberyn, Aegon) might have a different set of allocations. Meaning Eddard's assets are allocated across 5 indices (MSCI ACWI, EAFE, etc.) and Pycelle might have his assets allocated across 8.

    How do I handle this?

    I'm guessing setting a base variable name, like:

    For Each ErSel, set Sel1 = EdAllocation + n

    n +1

    or something like that... idk, I'm drowning over here!

    Fold 2:

    I need to capture the name for each selection. So 5% is MSCI ACWI, 15% is MSCI EAFE, etc.

    The reason for this is I need to perform an index(match) to Table2 to capture the price for specific dates. For YTD for example, I want to capture the most recent price max(table2[date]) and the earliest price (min,table2[date]... lol, the prices for these dates will have to be passed to a later formula which is below:

    All these variables, index(match)-es, min/max dates, all will add up to a formula, for demonstration sake, is below:

    EdSV * EdAllocation1 * (1+(maxdateprice - mindateprice)/mindateprice) + EdSV * EdAllocation2 * (1+(maxdateprice - mindateprice)/mindateprice)... EdSV * EdAllocation5 * (1+(maxdateprice - mindateprice)/mindateprice)

    I guess, now that I look at it, it might be easier to store the expression: (1+(maxdateprice - mindateprice)/mindateprice) as a variable, itself, lets call it EdYTDPercentChange, so written another way:

    EdSV * EdAllocation1 * EdYTDPercentChange + EdSV * EdAllocation2 * EdYTDPercentChange ... + EdSV * EdAllocation5 * EdYTDPercentChange = EdCP

    Then EdCP = ThisWorkbook.Sheets("Outputs").Range("E27")

    Sub MVUpdate()
    Dim EdSV As Integer
    Dim PySV As Integer
    Dim OMSV As Integer
    Dim AegSV As Integer

    ThisWorkbook.Sheets("Outputs").Range("K4").Value = EdSV
    ThisWorkbook.Sheets("Outputs").Range("L4").Value = PySV
    ThisWorkbook.Sheets("Outputs").Range("M4").Value = OMSV
    ThisWorkbook.Sheets("Outputs").Range("N4").Value = AegSV

    Dim EdR As Range, Er As Range, ErSel As Range

    Set EdR = ThisWorkbook.Sheets("Outputs").Range("K5:K20")
    Set ErSel = Nothing

    For Each Er In EdR
    If Er.Value <> "" Then
    If ErSel Is Nothing Then
    Set ErSel = Er
    Set ErSel = Union(ErSel, Er)
    End If
    End If
    Next Er
    If Not ErSel Is Nothing Then ErSel.Select

    End Sub