"Master" table to handle data entry

[Cardstang]

I have a fairly large spreadsheet that has many tables that are updated each month throughout the year. I'm using the word "table" to refer to a group of cells (each one 12 rows and 13 columns).

Individuals will scroll down to their respective table, provide updates, and then save and exit out of the spreadsheet.

What I'd like to do is have one "master" table that will handle all the entries, which will "send" the entries to the respective table. I'm also hoping this "master" table can be used to view the current entries where any changes can be made.

I've messed around with a dataform, but it's not exactly what I'm looking for.

Any suggestions?

Thanks!

[HansV]

Using a single "master" table is a good idea, but how to populate the "child" tables depends on the situation.

It might be possible to use autofilter to simply filter the table to display the relevant records, or to use advanced filter to copy specific records to another location, or a pivot table if you want to summarize data, or...

[Cardstang]

I don't think any type of a filter will work the way I want it to.

I've attached a sample. The structure is the same as what I'm working with, but there are 31 tables rather than just the few that are shown. Each one has a unique name, but the fields that are to be filled out are identical.

I've thought about a pivot table of sorts or even a series of countifs to summarize. What would be nice is to have one table that both "pushes" and "pulls" the data. I know this is getting real close to the functioning of a database, but I'm hoping to avoid using one.

[HansV]

It could be done with simple formulas. In the attached version, I have created a "master table" sheet, and then created the formulas for Alabama and Alaska in Sheet1. The other states haven't been modified.
If you edit a value in the master table sheet, the change will be refleceted in Sheet1, but not vice versa - it's not possible to "push" and "pull" at the same time.

Mastertable.xls

[Cardstang]

Thanks Hansv.

[BigKev]

The attached workbook contains code to update the data in both directions. It is a variation on code I used in another project and is actually mainly the work of Hans.

I created named ranges in the Sheet1 worksheet using the names of the states. I also created named ranges in the MasterTable worksheet for each of the states, the name being MD_ and the name of the state. The code in the ThisWorkbook module uses these named ranges to refer back and forward between the sheets.

The code assumes that the structure of the data in the worksheets is constant. For example, should you move the position of the state name in the Sheet1 worksheet from column E in the row above the data then it will either update incorreclty or fail spectacularly. Naturally if you change the names of the worksheets then, well, anything could happen.

Regards,
Kevin Bell

[Cardstang]

Thanks BigKev.

This is great.

And Kudos to HansV.