Macro to replace part of a formula

[Pedro Morais]

Hey guys,

I'm trying to create a macro to replace a link on a formula.
The formula is as below
='[Players Report 31121999.xls]Player1'!$AK$23

Where 31121999 is the date. there's a workbook everyday with different sheets for every player
I want to have a macro that gets the date I inputed in another cell and changes the date in the formula I displayed above (just changing the date).
Do you guys think thats possible?

Cheers
Pedro

 

[schielrn]

Not sure what the * is for. Is that the ' equivalent outside of the US Version of Excel maybe?

 

[Pedro Morais]

my bad...
typing:
31121999

 

[Greg Truby]

So what do you see if you type <ul>[*]="'[Players Report " & A1 & ".xls]Player1'!$AK$23"[/list]into a cell?

 

[Pedro Morais]

sorry bout the delay, just came back to the office...

I see "value"

 

[Pedro Morais]

sno..
regarding ="'[Players Report " & A1 & ".xls]Player1'!$AK$23"
i'm seing formula error

 

[Greg Truby]

Hmmmm

OK... let's try this. How 'bout if you type <ul>[*]="Hi " & A1[/list] into a cell? Do you get <ul type=square>[*]Hi 31121999[/list]

 

[Pedro Morais]

yep..
that worked out fine

hi 31121999

 

[Greg Truby]

OK... step 2... try<ul>[*]="'[Players Report " & A1 [/list] into a cell? Do you get <ul type=square>[*]'[Players Report 31121999[/list]

 

[Pedro Morais]

yep!

 

[Pedro Morais]

wrapping it up:

if I type

="'Players Report " & A1 & ".xls]Player1'!$AK$23"
I get: 'Players Report 23112007.xls]Player1'!$AK$23

If I add to that
=INDIRECT ("Players Report " & A1 & ".xls]Player1'!$AK$23")
i get: REF