Best approach to this problem
I was wondering if anyone has a suggestion as to the best solution to this
application. I have several spreadsheets tied together. One sheet is the Codes and is used for lookups in other files. The lookup formulas use this file with a named range which defines the list of codes. When I want to add a new Code I have to change the named range to reflect the added Code in every file that uses the lookup. Also, I copy some of the Codes to a hidden portion of some spreadsheets which I use for data validation lists in some cells to ensure there are no misspelled codes. These also have to be updated when a new code is entered. My current idea was to record a macro with an Excel Query to get the partial list in the hidden portion of the list which is called with the ThisWorkbook.Workbook_Open. The macro will also build the validation list and adjust the named range. Is there a better solution? |
Best approach to this problem
use dynamic defined names
Name: List1 Refersto: =Offset(Sheet1!$A$1,0,0,CountA(Sheet1:$A:$A),1) or setup your list in a database and use a querytable in your sheets to retrieve the data. -- Regards, Tom Ogilvy " wrote in message ... Another related question: When opening a file that has named ranges in Excel97 the program crashes if I answer Yes to Update External Data when the spreadsheet opens. Is there a solution to this other than opening the Codes file first? " wrote: I was wondering if anyone has a suggestion as to the best solution to this application. I have several spreadsheets tied together. One sheet is the Codes and is used for lookups in other files. The lookup formulas use this file with a named range which defines the list of codes. When I want to add a new Code I have to change the named range to reflect the added Code in every file that uses the lookup. Also, I copy some of the Codes to a hidden portion of some spreadsheets which I use for data validation lists in some cells to ensure there are no misspelled codes. These also have to be updated when a new code is entered. My current idea was to record a macro with an Excel Query to get the partial list in the hidden portion of the list which is called with the ThisWorkbook.Workbook_Open. The macro will also build the validation list and adjust the named range. Is there a better solution? |
Best approach to this problem
Sound like a good attack plan.
I might offer 1 suggestion to your delima. instead of adjusting your range name each time, have the range 2 to 3 time (or larger) the size the range needs to be to cover all your codes. this way you wont have to be re-seting the range all the time. -----Original Message----- I was wondering if anyone has a suggestion as to the best solution to this application. I have several spreadsheets tied together. One sheet is the Codes and is used for lookups in other files. The lookup formulas use this file with a named range which defines the list of codes. When I want to add a new Code I have to change the named range to reflect the added Code in every file that uses the lookup. Also, I copy some of the Codes to a hidden portion of some spreadsheets which I use for data validation lists in some cells to ensure there are no misspelled codes. These also have to be updated when a new code is entered. My current idea was to record a macro with an Excel Query to get the partial list in the hidden portion of the list which is called with the ThisWorkbook.Workbook_Open. The macro will also build the validation list and adjust the named range. Is there a better solution? . |
All times are GMT +1. The time now is 11:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com