Results 1 to 4 of 4

Thread: Formula help

  1. #1
    Join Date
    Feb 2004

    Unanswered: Formula help

    Heres an email ive sent to someone for some help but they havent got back to me yet so was wondering if anyone could help me

    All the information should be in the email

    Anyone got any ideas?


    Hi mate


    Heres the script

    Attached is 3 spreadsheets.

    (1) jpmemail.xls
    (2) brian fund details.xls
    (3) brian revals template.xls

    This is the sequence and purpose of each

    JP Morgan send us an email daily with a list of deals - this can change from
    day to day. Somedays reporting none other days reporting hundreds.

    I save the email they send me to my hardrive named jpmemail.xls (overwriting
    the previous days one)

    I then open my brian revals template.xls and this will automatically update
    due to the formulas i have in place on it.

    The problem is I need to manually drag down the formula each time in order
    to pick up everyday and vise versa if there is no deals and the previous day
    there was loads, i need to delete all the formula values to show this.

    Im looking for a formula to say that if there is (ie 12 deals on jpm
    email.xls you should drag down the formula 12times on the brian revals
    template.xls) and if there is only a couple of deals i need a formula to
    delete the formula so that it is only showing the deals.

    Can this be done?

    I cannot amend anything on JPM email as this is sent in from a company and
    is basically what i need to work with.

    The fund details.xls is just so i can pick up what fund code it is within
    our company so nothing needs to be done with this.

    Thanks again

  2. #2
    Join Date
    Dec 2003
    San Diego, CA
    Is it absolutely necessary to have this done through formulas?

    A quick and dirty method that I use is:
            Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
            Windows("Book1").Close False
        Application.CutCopyMode = False
    You can easily add a bit more code to automatically open the JPM workbook as well.

    Hope that helps,


    EDIT: Welcome to the Board!

  3. #3
    Join Date
    Feb 2004
    This method is fine

    But im really looking for the formula

    Because the spreadsheet after this one is going to need exactly what has just been copied and insert it behind the previous entry on a new spreadsheet containing a months worth of deals rather than a days.

    Any more help?

  4. #4
    Join Date
    Dec 2003
    San Diego, CA
    If you really need the formula approach, but don't want to have to recopy each day then you can always add a null qualifier to your existing formula, I.E. =IF([jpmemail.xls]Sheet1!A1="","",[jpmemail.xls]Sheet1!A1).

    Substitute my example for whatever your actual formula is and then copy in your reference workbook for as many cells as you've ever had maximum data from the JP Morgan e-mail.

    Note that this method will affect recalculation times vs. the VB approach.

    Hope that helps,


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts