Reference workbook by cell in a formula

[dsrt16]

I need to somehow reference a workbook name by a cell instead of typing in the workbook name to be referenced in the formula

I have created 9 workbooks each labeled a different week.

Each week has to reference the previous week(s) in a formula hosted in a cell. The formula, of course, works just fine.

However, the workbooks are currently called TemplateWeek1, TemplateWeek2, TemplateWeek3 etc.

Each teacher at my school will open TemplateWeek1 and type in their last name into a cell. When they do that, the workbook will automatically be saved their last name Week1. (MadsenWeek1). Same thing will happen for each workbook. (MadsenWeek2, MadsenWeek3 etc)

Now in TemplateWeek2 I have the following formula: =[TemplateWeek1.xls]A!$A$17
However, for MadsenWeek2 I need the formula to be =[MadsenWeek1.xls]A!$A$17

I don't want to have to go in and manually change each one to reflect the teacher's name after each teacher has saved their workbooks.

Therefore, I need to somehow reference the workbook by a cell reference. This is what I need, though I know this formula would not work. =['Set-up Page'!E6&"Week1".xls]A!$A$17

Where cell E6 on the Set-up Page contains the teacher's last name. Though the other problem is the teacher workbooks wouldn't even be created at the time. I just have the Template workbooks created, and when I train all the teachers on the program, they will create their workbooks.

As of right now, I just plan to go and individually open each workbook after that training session, and change the word Template to his/her last name in the formula.

Just wondering if their is another way, so I don't have to manually do that after the training session.

 

[Aladin Akyurek]

Something like:

=INDIRECT("["&'Set-up Page'!E6&"Week1.xlsx]A!A17")

If 'Set-up Page'!E6 houses e.g. Madsen, the workbook MadsenWeek1.xlsx must be open for the INDIRECT formula to work.

 

[dsrt16]

Oh ya I forgot about using indirect. However, the MadsenWeek1 workbook will not be open when they are looking at MadsenWeek2. They will only have the current weeks workbook open.

So if it Week1 has to be open, then this won't work.

 

[Aladin Akyurek]

Oh ya I forgot about using indirect. However, the MadsenWeek1 workbook will not be open when they are looking at MadsenWeek2. They will only have the current weeks workbook open.

So if it Week1 has to be open, then this won't work.

Two options regarding closed books...

1) If you are on 32bit system, try to install the morefunc.xll add-in and replace INDIRECT with INDIRECT.EXT.

2) Try to use the PULL code by Harlan Grove.

 

[dsrt16]

I could download morefunc.xll, but then I would have to download it on every computer at our school, so the udf would be the better route since I can put udf in modules and program it in.

However, I can not seem to find his udf code. The url I found when I google searched it ftp://members.aol.com/hrlngrv/pull.zip is not loading.

I found a few other url addresses, and they all did not come up. DO you know where I can get the udf code?

 

[Aladin Akyurek]

I could download morefunc.xll, but then I would have to download it on every computer at our school, so the udf would be the better route since I can put udf in modules and program it in.

Morefunc has the option to be included in the workbook one distributes.

However, I can not seem to find his udf code. The url I found when I google searched it ftp://members.aol.com/hrlngrv/pull.zip is not loading.

I found a few other url addresses, and they all did not come up. DO you know where I can get the udf code?

http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/

http://groups.google.com/group/microsoft.public.excel.worksheet.functions/msg/e249f6c074a3adfd