Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic List definition points to original file.
I'm having a problem with Dynamic range for a named list. I define a dynamic
range for a list that is referenced on a seperate worksheet as follow: Refers to: =OFFSET(Lists!$A$5,0,0,COUNTA(Lists!$A$4:$A$18),1) The cell reference in the 'COUNTA' formula is because I have header rows in the lists worksheet. A second 'Status' worksheet uses these lists in validation to create dropdown lists. When I copy both of the worksheets to another file, the list definition in the 'Status' worksheet is automatically being updated to continue to point to the original source file: Refers to: =OFFSET('[template.xls]Lists'!$A$5,0,0,COUNTA('[template.xls]Lists'!$A$4:$A$18),1) The 'Lists' worksheet seems to have the original definition but is now 'local' (In the Define Name dialog, the worksheet name is visible to the right of the list name.) Now if the original file is closed, the lists stop working. What's going on and how to I prevent Excel from automatically updating the list definition??? Thanks for any help, John S. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic List definition points to original file.
The source file *must* be open for it to work.
Solution: don't use another file as the source! Keep the source in the same file. If you must, then create an area that links to the other file: ='C:\TV\[test.xls]Sheet1'!$A1 ='C:\TV\[test.xls]Sheet1'!$A2 ='C:\TV\[test.xls]Sheet1'!$A3 etc Then use that range as the source. Biff "DocBrown" wrote in message ... I'm having a problem with Dynamic range for a named list. I define a dynamic range for a list that is referenced on a seperate worksheet as follow: Refers to: =OFFSET(Lists!$A$5,0,0,COUNTA(Lists!$A$4:$A$18),1) The cell reference in the 'COUNTA' formula is because I have header rows in the lists worksheet. A second 'Status' worksheet uses these lists in validation to create dropdown lists. When I copy both of the worksheets to another file, the list definition in the 'Status' worksheet is automatically being updated to continue to point to the original source file: Refers to: =OFFSET('[template.xls]Lists'!$A$5,0,0,COUNTA('[template.xls]Lists'!$A$4:$A$18),1) The 'Lists' worksheet seems to have the original definition but is now 'local' (In the Define Name dialog, the worksheet name is visible to the right of the list name.) Now if the original file is closed, the lists stop working. What's going on and how to I prevent Excel from automatically updating the list definition??? Thanks for any help, John S. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic List definition points to original file.
The point is I'm TRYING to keep the source in the same file. The intention is
to copy the 'Lists' worksheet and 'Status' worksheet to a new workbook and have the 'Status' worksheet reference the lists in the SAME NEW workbook. But when I copy the worksheets to a new workbook, Excel insists on updating the formula to point to to the original file which is what I DON'T want. I can't figure out how to keep Excel from automatically updating the formula. John S. "T. Valko" wrote: The source file *must* be open for it to work. Solution: don't use another file as the source! Keep the source in the same file. If you must, then create an area that links to the other file: ='C:\TV\[test.xls]Sheet1'!$A1 ='C:\TV\[test.xls]Sheet1'!$A2 ='C:\TV\[test.xls]Sheet1'!$A3 etc Then use that range as the source. Biff "DocBrown" wrote in message ... I'm having a problem with Dynamic range for a named list. I define a dynamic range for a list that is referenced on a seperate worksheet as follow: Refers to: =OFFSET(Lists!$A$5,0,0,COUNTA(Lists!$A$4:$A$18),1) The cell reference in the 'COUNTA' formula is because I have header rows in the lists worksheet. A second 'Status' worksheet uses these lists in validation to create dropdown lists. When I copy both of the worksheets to another file, the list definition in the 'Status' worksheet is automatically being updated to continue to point to the original source file: Refers to: =OFFSET('[template.xls]Lists'!$A$5,0,0,COUNTA('[template.xls]Lists'!$A$4:$A$18),1) The 'Lists' worksheet seems to have the original definition but is now 'local' (In the Define Name dialog, the worksheet name is visible to the right of the list name.) Now if the original file is closed, the lists stop working. What's going on and how to I prevent Excel from automatically updating the list definition??? Thanks for any help, John S. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic List definition points to original file.
I see. Well, I don't know how to stop Excel from doing that.
Biff "DocBrown" wrote in message ... The point is I'm TRYING to keep the source in the same file. The intention is to copy the 'Lists' worksheet and 'Status' worksheet to a new workbook and have the 'Status' worksheet reference the lists in the SAME NEW workbook. But when I copy the worksheets to a new workbook, Excel insists on updating the formula to point to to the original file which is what I DON'T want. I can't figure out how to keep Excel from automatically updating the formula. John S. "T. Valko" wrote: The source file *must* be open for it to work. Solution: don't use another file as the source! Keep the source in the same file. If you must, then create an area that links to the other file: ='C:\TV\[test.xls]Sheet1'!$A1 ='C:\TV\[test.xls]Sheet1'!$A2 ='C:\TV\[test.xls]Sheet1'!$A3 etc Then use that range as the source. Biff "DocBrown" wrote in message ... I'm having a problem with Dynamic range for a named list. I define a dynamic range for a list that is referenced on a seperate worksheet as follow: Refers to: =OFFSET(Lists!$A$5,0,0,COUNTA(Lists!$A$4:$A$18),1) The cell reference in the 'COUNTA' formula is because I have header rows in the lists worksheet. A second 'Status' worksheet uses these lists in validation to create dropdown lists. When I copy both of the worksheets to another file, the list definition in the 'Status' worksheet is automatically being updated to continue to point to the original source file: Refers to: =OFFSET('[template.xls]Lists'!$A$5,0,0,COUNTA('[template.xls]Lists'!$A$4:$A$18),1) The 'Lists' worksheet seems to have the original definition but is now 'local' (In the Define Name dialog, the worksheet name is visible to the right of the list name.) Now if the original file is closed, the lists stop working. What's going on and how to I prevent Excel from automatically updating the list definition??? Thanks for any help, John S. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need original file | Excel Discussion (Misc queries) | |||
How to update ORACLE query definition in Excel file | Links and Linking in Excel | |||
auto save excel file every 10 minutes to its original file name | Excel Discussion (Misc queries) | |||
Auto save replaced my original file and now I need the original? | Excel Discussion (Misc queries) | |||
Excel Reference points to original file | Excel Discussion (Misc queries) |