Formula help

mccrimmon · #1 (**permalink**) 02-19-04, 07:48

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

Ok

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
------------------------------------------------------------------------

Smitty · #2 (**permalink**) 02-19-04, 11:37

Is it absolutely necessary to have this done through formulas?

A quick and dirty method that I use is:

Code:

Cells.Copy
    Windows("Book2").Activate
        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,

Smitty

EDIT: Welcome to the Board!

mccrimmon · #3 (**permalink**) 02-20-04, 02:31

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?

Smitty · #4 (**permalink**) 02-20-04, 11:45

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,

Smitty

Internet Services

Web Development

Digital Marketing

Consumer Tech