Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 | |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
"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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Help creating dynamic refrence cells | Excel Worksheet Functions | |||
Flexible Cell Reference | Excel Discussion (Misc queries) | |||
Absolute Worksheet reference number | Excel Discussion (Misc queries) | |||
Cell Reference Math | Excel Worksheet Functions |