I am trying to develop a rather robust engineering/architectural style hardware schedule using Excel and data validation tables.
Due to the numerous options though, I feel that data validation tables will require far more maintenance than the benefit of developing this project. To work as well as hoped, the data list would ideally be self-updating, at least to some extent. There will just be far to many individual lists to create and keep up with to build a typical validation table.
I have attached two files here to aid in understanding my intent.
One titled: Master Hardware List.xlsm
One titled: Project book.xlsm
I envision using the Master Hardware List, consisting of several worksheets showing sub-catagorized hardware schedules, to build a company wide typical hardware schedule of all of our frequently used items.
I would like to build a data validation table within the Project Book, referencing back to the Master file, to be used for individual projects. Each project would have its own file and only be used for a specific job and / or client.
Due to the above mentioned concern regarding the number of variables, this would seem to require to much maintenance and data input to develop this through validation tables alone.
To describe my data within the Master Hardware Schedule.xlsm:
555# : provides a unique reference to each hardware entry.
Schedule : divides the hardware into multiple broad categories and corresponds to each of the sub-schedules on different worksheets.
Description, Manufacturer, Part number, length/size, and notes columns all further divide the data into specific specifications.
Ordering Specification, Finish, and 555 Notes columns are dependent upon the previous 6 choices.
My intention is to be able to build a project schedule within the Project Book.xlsm
Currently, the Project Book files has several worksheets.
First is the 'Project hardware Schedule'. This worksheet has no formatting / formulas and is meant to illustrate what I would like see for a completed schedule. I intend for an engineer to select hardware from this interface. Column A would be a static entry providing each hardware item added a unique job specific number. The second column would be a project specific drop-down pickbox reference (I have already completed this selection but removed the programing to avoid confusion). The process to fill in the remaing columns begins by selecting in Column C, the type of hardware required, ie:cabinetry hardware, then moving to the next column and selecting the description, ie: grommet, then moving to the next column and choosing the manufacturer, etc, etc. until a definitive spec is built for the item, with the rest of the columns filling in with the remaining data points.
Second, is 'MHS referenced tables'. This sheet is simply the 'Master hardware Schedule!' sub-schedules referenced into the Project book file. I had intended to try to build validation tables from data on this sheet
Third is a Pivot table based on 'MHS referenced tables'. I tried to see if there was a way to accomplish my goals through a pivot table. Doesn't seem like it works the way I want.
Fourth is Validation List - my start to creating the ranges, etc to build the validation drop down lists for 'Project hardware Schedule'
Fifth is Transposed list, an experiment at building automatically expanding validation ranges.
Since I am expecting this list to grow to several hundred, if not thousands, of entries with the resulting multitudes of different specifications required, I don't see how I can expect to implement this through the use of a typical Validation Table. So, is there a better way? VLOOKUP ? LIST Boxes ? Is it even possible with this many variables?
Although a novice at programming both Excel and VBA, I'm stubborn as hell and willing to beat my head until this works the way I would like it to work.
Please help tho, my head is sore already and I haven't made much progress in determining the correct direction to proceed.
Please note, the Project Book file will contain several other worksheets with additional job specific schedules and related information. I stripped them out of this file to avoid confusion.
Respectfully,
Joe
Due to the numerous options though, I feel that data validation tables will require far more maintenance than the benefit of developing this project. To work as well as hoped, the data list would ideally be self-updating, at least to some extent. There will just be far to many individual lists to create and keep up with to build a typical validation table.
I have attached two files here to aid in understanding my intent.
One titled: Master Hardware List.xlsm
One titled: Project book.xlsm
I envision using the Master Hardware List, consisting of several worksheets showing sub-catagorized hardware schedules, to build a company wide typical hardware schedule of all of our frequently used items.
I would like to build a data validation table within the Project Book, referencing back to the Master file, to be used for individual projects. Each project would have its own file and only be used for a specific job and / or client.
Due to the above mentioned concern regarding the number of variables, this would seem to require to much maintenance and data input to develop this through validation tables alone.
To describe my data within the Master Hardware Schedule.xlsm:
555# : provides a unique reference to each hardware entry.
Schedule : divides the hardware into multiple broad categories and corresponds to each of the sub-schedules on different worksheets.
Description, Manufacturer, Part number, length/size, and notes columns all further divide the data into specific specifications.
Ordering Specification, Finish, and 555 Notes columns are dependent upon the previous 6 choices.
My intention is to be able to build a project schedule within the Project Book.xlsm
Currently, the Project Book files has several worksheets.
First is the 'Project hardware Schedule'. This worksheet has no formatting / formulas and is meant to illustrate what I would like see for a completed schedule. I intend for an engineer to select hardware from this interface. Column A would be a static entry providing each hardware item added a unique job specific number. The second column would be a project specific drop-down pickbox reference (I have already completed this selection but removed the programing to avoid confusion). The process to fill in the remaing columns begins by selecting in Column C, the type of hardware required, ie:cabinetry hardware, then moving to the next column and selecting the description, ie: grommet, then moving to the next column and choosing the manufacturer, etc, etc. until a definitive spec is built for the item, with the rest of the columns filling in with the remaining data points.
Second, is 'MHS referenced tables'. This sheet is simply the 'Master hardware Schedule!' sub-schedules referenced into the Project book file. I had intended to try to build validation tables from data on this sheet
Third is a Pivot table based on 'MHS referenced tables'. I tried to see if there was a way to accomplish my goals through a pivot table. Doesn't seem like it works the way I want.
Fourth is Validation List - my start to creating the ranges, etc to build the validation drop down lists for 'Project hardware Schedule'
Fifth is Transposed list, an experiment at building automatically expanding validation ranges.
Since I am expecting this list to grow to several hundred, if not thousands, of entries with the resulting multitudes of different specifications required, I don't see how I can expect to implement this through the use of a typical Validation Table. So, is there a better way? VLOOKUP ? LIST Boxes ? Is it even possible with this many variables?
Although a novice at programming both Excel and VBA, I'm stubborn as hell and willing to beat my head until this works the way I would like it to work.
Please help tho, my head is sore already and I haven't made much progress in determining the correct direction to proceed.
Please note, the Project Book file will contain several other worksheets with additional job specific schedules and related information. I stripped them out of this file to avoid confusion.
Respectfully,
Joe
