Macro to replace part of a formula

schielrn

Well-known Member

Joined

Apr 4, 2007

Messages

6,941

• Dec 6, 2007

• #2

Welcome to the Board!

Why not use indirect without usign a macro. Not sure what cell your inputted date is in, but I have just assumed A1:

Code:

=Indirect("'[Players Report " & A1 & ".xls]Player1'!$AK$23")

Change the cell reference as necessary.

Also with the morefunc add-in you can use Indirect.Ext() and it will pull values from a closed workbook. Morefunc is located at this website if you want to download it:

http://xcell05.free.fr/english/

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,025

• Dec 6, 2007

• #3

Yes, you can certainly do what you're asking using VBA. A non-VBA alternative would be to the date of interest in a cell and name the cell MyDate or something similarly brilliant and alter the formula to read: <ul>[*]=Indirect("'[Players Report " & MyDate & ".xls]Player1'!$AK$23")[/list]Then as you change the date, the formula will change.

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,025

• Dec 6, 2007

• #4

schielrn, a rather brilliant suggestion you made there. :wink: You might wanna edit it to put a double quote right in front of the closing parenthesis.

^edit Dagnabit, 2^nd time today I'm a fergettin' me blasted manners... Welcome to MrExcel, Pedro! ...sheesh... _/edit

 

Upvote 0

P

Pedro Morais

Board Regular

Joined

Dec 5, 2007

Messages

90

• Dec 6, 2007

• #5

Thanks

wow... i already tried to get parts of text & cell value together but I didn't know how.. I tried concatenate :S ...

Anyways, I copied the code and even inputed the date in cell A1, but it says the formula has an error...

Any idea why?
Thanks a mil

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,025

• Dec 6, 2007

• #6

You might need to alter the formula to sumpin' like:<ul>[*]=Indirect("'[Players Report " & Text(A1,"ddmmyyyy") & ".xls]Player1'!$AK$23")[*]=Indirect("'[Players Report " & Text(MyDate,"ddmmyyyy") & ".xls]Player1'!$AK$23")[/list]

 

Upvote 0

schielrn

Well-known Member

Joined

Apr 4, 2007

Messages

6,941

• Dec 6, 2007

• #7

Thanks for noticing that missing quote Greg. I have now edited. But now I think you have it right with the text format. i forgot that usually needs to be done when using indirect because I believe it is going to take the dates serial number or how it is formatted in the cell, but I just didn't test it.

 

Upvote 0

P

Pedro Morais

Board Regular

Joined

Dec 5, 2007

Messages

90

• Dec 6, 2007

• #8

mmm not there yet...

the result of the part Text(A1,"ddmmyyyy") is #value...

A1 is formatted as "General"... does that interfer?

Sorry bout my inexperience guys :P

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,025

• Dec 6, 2007

• #9

What are you typing into A1? <ul>[*]31121999 ?[*]12/31/99 ?[/list]If you type just <ul>[*]="'[Players Report " & Text(A1,"ddmmyyyy") & ".xls]Player1'!$AK$23"[/list]into a cell what do you get?

 

Upvote 0

P

Pedro Morais

Board Regular

Joined

Dec 5, 2007

Messages

90

• Dec 6, 2007

• #10

typing

* 31121999

I get #Value too..

 

Upvote 0