Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filesize: embedded data validation lists all stored independently?
I have 4 named ranges that are used as sources for data validation lists. On
my worksheet, I have: 120 cells associated with a named range with 2 items, 60 cells associated with a named range that will have (on average) about 50 items 10 cells associated with a named range with about 20 items, and 60 cells associated with a named range containing about 4000 items Does each data validation cell store a copy of the full list, as well as the currently selected index value, or does the sheet (or entire workbook) share one 'copy' of the named range, with each data validation cell only using as much memory as it takes to store the index value for that cell? I tried deleting sections of the data validation cells, but it didn't affect the filesize, so either the file is storing undo information, or only the index is stored and my lists are taking a lot more memory than I ever would have expected. I appreciate any information available on the use of memory by data validation list cells based on named ranges. Thanks! Keith |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filesize: embedded data validation lists all stored independently?
I don't actually know the definitive answer, but I can infer some things. In
the bottom line analysis, Excel actually only looks at one cell at a time and pulls up the necessary information about it 'as required'. That's a simplistic view, but it allows us to infer that the entire lists are not stored in each cell, just a reference to their source. One thing Excel does is to 'remember' the largest row number/farthest right column ever used, even when you delete entries from them. Often to reduce a file's size, you have to actually delete unused rows/columns, save the file and reopen it to see a reduction in file size. Check out this FAQ over at Contextures for more information about this situation - and cures for it: http://www.contextures.com/xlfaqApp.html#Unused "ker_01" wrote: I have 4 named ranges that are used as sources for data validation lists. On my worksheet, I have: 120 cells associated with a named range with 2 items, 60 cells associated with a named range that will have (on average) about 50 items 10 cells associated with a named range with about 20 items, and 60 cells associated with a named range containing about 4000 items Does each data validation cell store a copy of the full list, as well as the currently selected index value, or does the sheet (or entire workbook) share one 'copy' of the named range, with each data validation cell only using as much memory as it takes to store the index value for that cell? I tried deleting sections of the data validation cells, but it didn't affect the filesize, so either the file is storing undo information, or only the index is stored and my lists are taking a lot more memory than I ever would have expected. I appreciate any information available on the use of memory by data validation list cells based on named ranges. Thanks! Keith |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Filesize: embedded data validation lists all stored independen
I appreciate your insight into the way the data validation lists use memory -
I haven't seen or read anything about this before, so I was completely in the dark on whether it would have an impact. For this particular worksheet, I had tried both clearing and deleteing extra rows and columns, to no effect (Excel 2003). I then cleared even the used space (about 30 rows, and less than 30 columns), and the file size was not reduced. Removing the worksheet did reduce filesize significantly. What finally worked for me was copy/pasting the used area into a new sheet, after which I only had to reformat column widths and row heights- then I deleted the original worksheet. I now have a fully functional workbook that takes up about half the space. I wish there was a way to determine the real root cause (was it related to creating the workbook in 2007 with lots of formatting, then bringing it to 2003, was it a file corruption that was somehow specific to that worksheet, etc...) but for now I'll just be glad that the filesize is significantly smaller. Thanks! Keith "JLatham" wrote: I don't actually know the definitive answer, but I can infer some things. In the bottom line analysis, Excel actually only looks at one cell at a time and pulls up the necessary information about it 'as required'. That's a simplistic view, but it allows us to infer that the entire lists are not stored in each cell, just a reference to their source. One thing Excel does is to 'remember' the largest row number/farthest right column ever used, even when you delete entries from them. Often to reduce a file's size, you have to actually delete unused rows/columns, save the file and reopen it to see a reduction in file size. Check out this FAQ over at Contextures for more information about this situation - and cures for it: http://www.contextures.com/xlfaqApp.html#Unused "ker_01" wrote: I have 4 named ranges that are used as sources for data validation lists. On my worksheet, I have: 120 cells associated with a named range with 2 items, 60 cells associated with a named range that will have (on average) about 50 items 10 cells associated with a named range with about 20 items, and 60 cells associated with a named range containing about 4000 items Does each data validation cell store a copy of the full list, as well as the currently selected index value, or does the sheet (or entire workbook) share one 'copy' of the named range, with each data validation cell only using as much memory as it takes to store the index value for that cell? I tried deleting sections of the data validation cells, but it didn't affect the filesize, so either the file is storing undo information, or only the index is stored and my lists are taking a lot more memory than I ever would have expected. I appreciate any information available on the use of memory by data validation list cells based on named ranges. Thanks! Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation Lists | Excel Worksheet Functions | |||
data validation lists | Excel Discussion (Misc queries) | |||
Data Validation - Using 2 Lists | Excel Discussion (Misc queries) | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
data validation lists | Excel Discussion (Misc queries) |