Creating your own reference

[RBHicks]

Trying to do something a little complicated here and honestly not even
sure if this would be possible. What I need is for one workbook to
create another and then reference this second workbook for populating
certain cells.

I have a list of employees in A5:A8. Each employee has certain
statistics that are shown in columns C:K. I have already written the
formula which creates a second workbook (named test.xls at this point)
and then creates a separate sheet in the workbook for each employee
listed in the primary workbook.

My problem arises in trying to then reference anything from test.xls in
the primary workbook. I have a cell in the primary workbook which is
populated with the name of the created workbook (as this will not
always be the same after testing).
However, I cannot find a way to write a function that will look at a
cell to find the name of the workbook or sheet it is referencing.

Does anyone know if this is possible or how to accomplish this?

Many thanks.

 

[Bob Phillips]

Keep the initial workbook active

Set newBook = Workbooks.Add
For Each cell In Range("A5:A8")
newBook.Worksheets.Add,Name = cell.Value
Next cell


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[ScottO]

Have you tried using the INDIRECT function?
Rgds,
ScottO

|
| Trying to do something a little complicated here and honestly not even
| sure if this would be possible. What I need is for one workbook to
| create another and then reference this second workbook for populating
| certain cells.
|
| I have a list of employees in A5:A8. Each employee has certain
| statistics that are shown in columns C:K. I have already written the
| formula which creates a second workbook (named test.xls at this point)
| and then creates a separate sheet in the workbook for each employee
| listed in the primary workbook.
|
| My problem arises in trying to then reference anything from test.xls in
| the primary workbook. I have a cell in the primary workbook which is
| populated with the name of the created workbook (as this will not
| always be the same after testing).
| However, I cannot find a way to write a function that will look at a
| cell to find the name of the workbook or sheet it is referencing.
|
| Does anyone know if this is possible or how to accomplish this?
|
| Many thanks.
|
|
| --
| RBHicks
| ------------------------------------------------------------------------
| RBHicks's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12314
| View this thread: http://www.excelforum.com/showthread.php?threadid=384384
|

 

[RBHicks]

Set newBook = Workbooks.Add
For Each cell In Range("A5:A8")
newBook.Worksheets.Add,Name = cell.Value
Next cell

That's how I created the second workbook test.xls, however my problem
arises with then referencing this test.xls from the original.xls cells
in a formula.

Have you tried using the INDIRECT function?
Rgds,
ScottO

Will try using that function to see if it will perform the needed task.
Many thanks.

 

[Bob Phillips]

Will try using that function to see if it will perform the needed task.
Many thanks.

INDIRECT does not work with a closed workbook!