Posts by MysticGenius

    Hi,


    I have two columns of data, one is spend, the second is Goals achieved for that level of spend. As you can see there is no linear pattern to the goals vs spend.


    Can someone give me a formula to calculate the optimal spend?


    Thanks





    Spend Goals [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 101"]$4,220[/TD]
    [TD="width: 57"]30[/TD]

    [/tr]


    [tr]


    [td]

    $4,250

    [/td]


    [td]

    22

    [/td]


    [/tr]


    [tr]


    [td]

    $4,252

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [tr]


    [td]

    $4,266

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [tr]


    [td]

    $4,334

    [/td]


    [td]

    22

    [/td]


    [/tr]


    [tr]


    [td]

    $4,362

    [/td]


    [td]

    0

    [/td]


    [/tr]


    [tr]


    [td]

    $4,376

    [/td]


    [td]

    4

    [/td]


    [/tr]


    [tr]


    [td]

    $4,380

    [/td]


    [td]

    15

    [/td]


    [/tr]


    [tr]


    [td]

    $4,381

    [/td]


    [td]

    19

    [/td]


    [/tr]


    [tr]


    [td]

    $4,392

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    $4,437

    [/td]


    [td]

    22

    [/td]


    [/tr]


    [tr]


    [td]

    $4,447

    [/td]


    [td]

    33

    [/td]


    [/tr]


    [tr]


    [td]

    $4,465

    [/td]


    [td]

    15

    [/td]


    [/tr]


    [tr]


    [td]

    $4,479

    [/td]


    [td]

    22

    [/td]


    [/tr]


    [tr]


    [td]

    $4,527

    [/td]


    [td]

    4

    [/td]


    [/tr]


    [tr]


    [td]

    $4,537

    [/td]


    [td]

    19

    [/td]


    [/tr]


    [tr]


    [td]

    $4,544

    [/td]


    [td]

    33

    [/td]


    [/tr]


    [tr]


    [td]

    $4,574

    [/td]


    [td]

    26

    [/td]


    [/tr]


    [tr]


    [td]

    $4,576

    [/td]


    [td]

    19

    [/td]


    [/tr]


    [tr]


    [td]

    $4,629

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    $4,634

    [/td]


    [td]

    41

    [/td]


    [/tr]


    [tr]


    [td]

    $4,658

    [/td]


    [td]

    37

    [/td]


    [/tr]


    [tr]


    [td]

    $4,660

    [/td]


    [td]

    19

    [/td]


    [/tr]


    [tr]


    [td]

    $4,662

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [tr]


    [td]

    $4,665

    [/td]


    [td]

    19

    [/td]


    [/tr]


    [tr]


    [td]

    $4,685

    [/td]


    [td]

    82

    [/td]


    [/tr]


    [tr]


    [td]

    $4,687

    [/td]


    [td]

    26

    [/td]


    [/tr]


    [tr]


    [td]

    $4,700

    [/td]


    [td]

    19

    [/td]


    [/tr]


    [tr]


    [td]

    $4,704

    [/td]


    [td]

    26

    [/td]


    [/tr]


    [tr]


    [td]

    $4,715

    [/td]


    [td]

    33

    [/td]


    [/tr]


    [tr]


    [td]

    $4,728

    [/td]


    [td]

    15

    [/td]


    [/tr]


    [tr]


    [td]

    $4,783

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    $4,795

    [/td]


    [td]

    30

    [/td]


    [/tr]


    [tr]


    [td]

    $4,801

    [/td]


    [td]

    26

    [/td]


    [/tr]


    [tr]


    [td]

    $4,807

    [/td]


    [td]

    56

    [/td]


    [/tr]


    [tr]


    [td]

    $4,849

    [/td]


    [td]

    19

    [/td]


    [/tr]


    [tr]


    [td]

    $4,859

    [/td]


    [td]

    7

    [/td]


    [/tr]


    [tr]


    [td]

    $4,865

    [/td]


    [td]

    33

    [/td]


    [/tr]


    [/TABLE]

    Hi,


    I have a column of dates which has 2 formats in it for some reason half are UK and half are US.


    I want to change them all to UK format.


    I have tried to use:


    Code
    =IF(MID(A2,4,2)>=13,(MID(A2,4,2)&"/"&LEFT(A2,2)&"/"&RIGHT(A2,2))+0,IFERROR(MID(A2,4,2),A2))


    and this converts the US dates to the UK, but it gives me #value when it hits the UK formatted date rather than just using the Date as It is.


    Please see the attached sample.


    Thanks

    Re: Open wod docs in a folder, enter password and save with password removed


    Quote from cytop;755923

    I know it'll fail - it wasn't written to accomodate sub-directories in multiple folders.


    Answering queries on forums like this is a little like electricity - the answers always take the path of least resistance. Sometimes (like now), the unmentioned requirement can radically affect the solution. It needs a bit of a re-write.


    Judging by the log you posted, I guess it's after 6:00pm and home time. There'll be something for you tomorrow.


    Thank you muchly sir

    Re: Open wod docs in a folder, enter password and save with password removed


    Quote from cytop;755915

    Nah, we all do things like that at times. But can I reserve the right to quote you in future? :)


    Adding code in Word is the same as Excel, Alt-F11 for the VBA environment and just paste the code, cursor somewhere in the code and press F5.

    Yeah,


    u can quote me on that in the future!!


    I have one last question for you, how can i attach the VBA to a command button in word, so I dont have to jump into developer to run it!


    I have the command button in my document but i cant seem to assign a macro to it like i can with excel.


    TIA

    Re: Open wod docs in a folder, enter password and save with password removed



    Hi,


    This is the output i get:


    2015Sep08 17:27:01 Started
    2015Sep08 17:27:13 Password=caliber
    2015Sep08 17:27:13 Directory exists
    2015Sep08 17:27:13
    2015Sep08 17:27:13 File: ICTNWK505_AMa.doc
    2015Sep08 17:27:23 ActiveX component can't create object
    2015Sep08 17:27:23 Finished

    Re: Open wod docs in a folder, enter password and save with password removed


    Quote from cytop;755902

    Comment out the line

    Code
    ' If Err.Number = 4605 Then Resume ResumePoint


    by adding an Apostrophe at the beginning of the line, then see if any messages are displayed.


    Hi,


    thanks, no errors are displayed.


    Is there possible a referance library i need to enable?


    I have the following:


    Visual Basic For Applications
    MS Excel 14.0 Object Library
    OLE Automation
    MS Office 14.0 Object Library
    MS Word 14.0 Object Library
    Windows Script Host Object Model


    Quote from cytop;755902


    It's pointless trying to expand this until it works for 1 directory but you really should have mentioned that to begin with as it will need to be rewritten.

    Apologies, i was told that the documents were all in one folder, however they didnt tell me they were in sub directories.


    Apologies again!


    Thanks for the help

    Hi,


    I have a situation, an employee has left and gone overseas. I have about 200+ word documents that i need to open and remove the password from.


    I have the password, and all of the documents are in the same directory, what i need to do is:


    1: Open the XXX.doc
    2: Enter the password (They all have the same password)
    3: Remove the password
    4: Save the document without its password


    Is this possible with VBA?


    Thanks in advance

    Re: CPI Calculation with variable CPI over multiple years


    Quote from AlanSidman;729004

    Understand. Look at the updated file.


    This is looking really good, but it is calculating CPI assuming that the rental start period is 1972.


    What is needs to do is the "Select Period" cell is the date the rent starts, so if the rent "Select Period" is Sept-1998, it should calculate CPI from sept-1998 through to current day.


    I notice that if i put in the start period as mar-2014 with $100 rent the current value is calculated at $171.99 which is incorrect as it should only have increased by the CPI in Jun-2014 which was 0.006% so the resulting answer should be $100.6


    is there any way to do this?


    thanks

    Re: CPI Calculation with variable CPI over multiple years


    Quote from AlanSidman;728931

    Look at the attached. Is this what you are looking for?


    Those calcs look perfect, thats what I had initially, I have updated the sheet to include a table.


    I dont think i was very clear in my initial post, so apologies for that, that i need is to be able to select the month/year from the table, then enter the $ amount and have the current rate in the next cell.


    Please can you see the updated sheet attached.


    thanks so much for your reply and help.

    Re: index and match help



    thanks very much, very clear