trishcollins
Board Regular
- Joined
- Jan 7, 2006
- Messages
- 71
I have a spreadsheet that contains a worksheet called variables, which I created tables and named ranges, and used them as drop down lists via the data validation feature. Works fine. I like to use tables and then name the data column in the table, so that if I add or delete rows in the table, the named range changes dynamically.
This spreadsheet had a worksheet to track section deliverables, and a worksheet to track section decisions. I had macros running to export reports and set up cells to contain the location of where to store the report, which in turn became variables in the report macro. It was all contained in one sheet and worked fine.
Now I have been asked to separate out the decision sheet from the deliverables sheet and provide both for use by other sections to use. The reports will be run individually, and someone will stich them together. Obviously, a multi-user database would be a better option for this, but for lack of tools, it's Excel.
Since both the decision registry and the deliverables tracking sheet use the same variables such as resource names, section names, deliverable types, etc., I figured if I separated out the variables sheet and provided it in a common location, and pointed all the other sheets to it, if I needed to change, add, or delete any variable item (add the name of a new person, change the name of a service, delete a particular deliverable type), I could maintain that in one location, and not have to get people to manually change their individual spreadsheets, which is a nightmare. I have two options, I can either have each spreadsheet automatically update their respective variables spreadsheet from the common one, or I can simply use Indirect to use the named ranges from the common file. I am currently testing the Indirect function.
The first thing I did was split the deliverables and decision registry into two workbooks, but kept the variables worksheet with each, so they would still function properly and of course, that works fine. Then I saved a copy of the variables worksheet as a separate workbook, so it retained all the same table names and named ranges. Now I have started testing, but I am running into problems.
In the data validation for the cell to allow the user to pick the resource, I have entered:
=INDIRECT("'[EA Common Variables.xlsm]Variables'!Resource_Names")
Instead of getting the Resource Names in the drop down menu, I am getting a drop down list that is actually 5 columns to the LEFT of the "Resource_Names" defined range. To see if this was consistent, I tried another data validation for section:
=INDIRECT("'[EA Common Variables.xlsm]Variables'!Section_Data")
In this case, it is returning a range of data 12 columns to the right. It actually presents the entire range of the new data, which contains a different number of items, but a completely different range with a completely different name.
Any thoughts?
Once I get this working, I want to substitute a variable name for the directory and filename, so if it's changed, I won't have to update all the data validation.
I have stored the filename and location in a cell, and named the range "Common_Variables", so I in the data validation formula, I would like to substitute '[EA Common Variables.xlsm]Variables' with 'Common_Variables'. I will assume that's another Indirect, but before I work on that, I need to get the data validation working.
If this is just impossible, I may have to simply write a macro that when the workbook is opened, it goes out and makes a copy of the existing variable worksheet and replaces the current one. Any thoughts as to which one is better?
This spreadsheet had a worksheet to track section deliverables, and a worksheet to track section decisions. I had macros running to export reports and set up cells to contain the location of where to store the report, which in turn became variables in the report macro. It was all contained in one sheet and worked fine.
Now I have been asked to separate out the decision sheet from the deliverables sheet and provide both for use by other sections to use. The reports will be run individually, and someone will stich them together. Obviously, a multi-user database would be a better option for this, but for lack of tools, it's Excel.
Since both the decision registry and the deliverables tracking sheet use the same variables such as resource names, section names, deliverable types, etc., I figured if I separated out the variables sheet and provided it in a common location, and pointed all the other sheets to it, if I needed to change, add, or delete any variable item (add the name of a new person, change the name of a service, delete a particular deliverable type), I could maintain that in one location, and not have to get people to manually change their individual spreadsheets, which is a nightmare. I have two options, I can either have each spreadsheet automatically update their respective variables spreadsheet from the common one, or I can simply use Indirect to use the named ranges from the common file. I am currently testing the Indirect function.
The first thing I did was split the deliverables and decision registry into two workbooks, but kept the variables worksheet with each, so they would still function properly and of course, that works fine. Then I saved a copy of the variables worksheet as a separate workbook, so it retained all the same table names and named ranges. Now I have started testing, but I am running into problems.
In the data validation for the cell to allow the user to pick the resource, I have entered:
=INDIRECT("'[EA Common Variables.xlsm]Variables'!Resource_Names")
Instead of getting the Resource Names in the drop down menu, I am getting a drop down list that is actually 5 columns to the LEFT of the "Resource_Names" defined range. To see if this was consistent, I tried another data validation for section:
=INDIRECT("'[EA Common Variables.xlsm]Variables'!Section_Data")
In this case, it is returning a range of data 12 columns to the right. It actually presents the entire range of the new data, which contains a different number of items, but a completely different range with a completely different name.
Any thoughts?
Once I get this working, I want to substitute a variable name for the directory and filename, so if it's changed, I won't have to update all the data validation.
I have stored the filename and location in a cell, and named the range "Common_Variables", so I in the data validation formula, I would like to substitute '[EA Common Variables.xlsm]Variables' with 'Common_Variables'. I will assume that's another Indirect, but before I work on that, I need to get the data validation working.
If this is just impossible, I may have to simply write a macro that when the workbook is opened, it goes out and makes a copy of the existing variable worksheet and replaces the current one. Any thoughts as to which one is better?
