Hello,
I'm a consultant working for a city. I have a Workbook spreadsheet (Tab) that contains parcel data for this city, such as parcel number, StreetName, house number, full address, zoning category, etc. I've sorted the data StreetName thereby grouping all of 1st Street, 2nd Street, Smith St, etc. My first tab is set up to show all streets, grouped/sorted by StreetNames. This tab is called All_Streets

I need to display data for each separate Street on a separate page (Tab). To this end, I created a separate tab for many street names, then I cut data from Smith Street rows (on the All_Streets tab and Paste > Link onto a SmithStreet tab, repeating this for various streets on various StreetName tabs.

When I deliver this finished Workbook to the City, they will periodically need to Edit data in the file. Not being power users, it will be easiest for them to do their Edits on the separate StreetName tabs, rather that deal with the full table/spreadsheet.

Here's my challenge, or my question:
Is there a way to configure the workbook and tabs so that updates can be made either in the full table (All_Streets tab) or on the StreetName tabs *where edits in either location will cause an update in the other?* Ideally I'd like these edits & updates to work both directions.

Please share any ideas you have on how to set up bi-directional editing and updates.

Thanks much, in advance...


Dave

I really don't see the point of having all these tabs.

Surely it would be simpler to use a single worksheet with containing two columns: one for each street and the other for the suburb, with autofilters on each, so the users can choose all streets for one or more suburbs, or all suburbs with the same street name?

If someone needs to add a new street, that can be done anywhere, by inserting a new row with the suburb & name data, even when the filters are on.

See attached demo using US States & Locales. Even a novice will soon learn to use the filters.

Attached Files

Filters.xlsx (71.4 KB, 11 views)

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

The spreadsheet needs to show about 10 columns of data for each parcel (address), sorted by street.

Reason for the Tabs:
The Spreadsheet Users will be City staff. They have a middle to low level of Excel capabilities. They'll need to view and update data in the 10 columns related to each address. Viewing data street by street is easier and what they're accustomed to.

They're:
-Not likely to handle filtering by Street Name very well
-Overwhelmed by working in the full table showing thousands of addresses
-Accustomed to seeing addresses organized by streets

The number of columns is inconsequential for filtering.

Even in the short term, teaching your users to use filters (probably less than 5 minutes required) will be far more productive than your present arrangement. I didn't say they would have to filter by street name; I said they could. Given that streets often cross locale boundaries, the ability to filter by street means they don't need to find the locale first. This can be very useful for a user who isn't quite sure which locale a particular part of a street is in.

As for users being "Overwhelmed by working in the full table showing thousands of addresses", methinks you protest too much. The only time they'll be able see that data is if they have no filtering. This is no different than if they switch to the sheet containing all the data in your existing workbook anyway. With filtering, they'll only ever have one sheet to work with, not a plethora of them.

The filtering has no impact on whether users see addresses organised by streets, so your concerns there are groundless.

The two-way updates, that would require some complex macro programming and would immediately break down for any user who either doesn't allow them to run or has their macro security options set too high.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]