Creating your own reference
 

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...o&userid=12314
View this thread: http://www.excelforum.com/showthread...hreadid=384384

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)


"RBHicks" wrote in
message ...

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...o&userid=12314
View this thread: http://www.excelforum.com/showthread...hreadid=384384

Have you tried using the INDIRECT function?
Rgds,
ScottO

"RBHicks" wrote in message
...
|
| 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...o&userid=12314
| View this thread: http://www.excelforum.com/showthread...hreadid=384384
|

Bob Phillips Wrote:
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.

ScottO Wrote:
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.


--
RBHicks
------------------------------------------------------------------------
RBHicks's Profile: http://www.excelforum.com/member.php...o&userid=12314
View this thread: http://www.excelforum.com/showthread...hreadid=384384

"RBHicks" wrote in
message ...
ScottO Wrote:
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.


INDIRECT does not work with a closed workbook!