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

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

$4,250

22

$4,252

7

$4,266

7

$4,334

22

$4,362

0

$4,376

4

$4,380

15

$4,381

19

$4,392

30

$4,437

22

$4,447

33

$4,465

15

$4,479

22

$4,527

4

$4,537

19

$4,544

33

$4,574

26

$4,576

19

$4,629

30

$4,634

41

$4,658

37

$4,660

19

$4,662

7

$4,665

19

$4,685

82

$4,687

26

$4,700

19

$4,704

26

$4,715

33

$4,728

15

$4,783

30

$4,795

30

$4,801

26

$4,807

56

$4,849

19

$4,859

7

$4,865

33

[/TABLE]

Re: Converting US to UK dates

I found a solution:

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

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:

=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 Word docs in a folder, enter password and save with password removed

Quote from cytop;756016

Sorry about that - one of those "Nah - too simple to error" errors that wasn't tested. Happened when it tried to process a file in a sub-directory. Fix attached.

thats absolutely perfect!! thanks for all your help!!!

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

That looks amazing!

I got this error:

[ATTACH=CONFIG]66868[/ATTACH]

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

... what about the sub-directories?

it failed with the sub dirs,

i got this output:

2015Sep08 18:06:22 Started
2015Sep08 18:06:29 Password=caliber
2015Sep08 18:06:29 Directory exists
2015Sep08 18:06:29 Finished

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

It worked perfect!!!

Thanks so much

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

Quote from cytop;755913

Are you running this in Word or Excel...?

excel.....im guessing it should be work......

Im understanding your comment "Word VBA"

I feel like such a dumbass!!!!

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

Quote from cytop;755910

2015Sep08 08:08:37 Started
2015Sep08 08:08:39 Password=Test
2015Sep08 08:08:39 Source directory=C:\temp\test\
2015Sep08 08:08:39 SAVED Directory exists
2015Sep08 08:08:39
2015Sep08 08:08:39 File: test1.doc
2015Sep08 08:08:39 Opened
2015Sep08 08:08:40 Saved as C:\temp\test\Saved\test1.doc
2015Sep08 08:08:40 Closed
2015Sep08 08:08:40
2015Sep08 08:08:40 File: test1.docx
2015Sep08 08:08:40 Opened
2015Sep08 08:08:40 Saved as C:\temp\test\Saved\test1.docx
2015Sep08 08:08:40 Closed
2015Sep08 08:08:40 Finished

Display More

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

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

Quote from cytop;755862

Word VBA...

There seems to be an issue, It allows me to enter the password, It creates the "Saved" Folder but nothing gets placed into the saved folder.

Also is there any way to expand it to look in a folder with sub folders?

Thanks in advance

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

Let me know if this is the number you are looking for?

this looks good, ill test it against some manual calcs and let you know how i go!!!

thanks for your help!

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

Understand. Look at the updated file.

thats epic thanks!! it seems so simple now!

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.

Hi all,

I am trying to calculate rental CPI over multiple years with variable CPI.

I have attached the data i have acquired from the Australian Bureau of Statistics.

What i need to do is, for example, if the rent was $100 in 1986, what would it be today.

Thanks in advance for help

Re: index and match help

Quote from danerida;725840

=INDEX("THE TABLE OF DATA","ROW NUMBER IN THE TABLE", "COLUMN NUMBER IN THE TABLE")
=MATCH("THE VALUE TO LOOK UP","WHERE TO LOOK IT UP", 0= EXACT MATCH)
=INDIRECT("THE RANGE REFERENCED BY YOUR NAMED RANGE")

Therefore:

=INDEX("YOUR TABLE", MATCH("VALUE IN D5 OF INPUT SHEET", "LOOK IN THE NAMED RANGE REFERENCED BY C5 OF INPUT SHEET", "0 FOR AN EXACT MATCH") + 1 "TO GO 1 ROW LOWER BECAUSE OF YOUR BLANK ROW", MATCH("VALUE IN C5 OF INPUT SHEET", "LOOK IN ROW 1 OF THE TABLE (headers)", "0 FOR AN EXACT MATCH") + 1 "TO GO 1 COLUMN TO THE RIGHT BECAUSE OF THE BLANK COLUMN")

Hopefully that all makes sense.

Dan

Display More

thanks very much, very clear