How Can I: Add a ROW to MASTER table and Auto-Populate Child?

[Sandcastle]

Here's the data:

1. I have a single document with several sheets inside.
2. One sheet contains the master table that holds the Store IDs. This is the main bit of data.
3. Each sheet in the doc has a table that uses the Store IDs. I'm calling these the child tables.
4. Each time I add a new store to the master table I want that newly added store to automatically appear within the child tables.
5. Not all tables in the sheet have/use the same data, but they all must display the store's ID, therefore when a new store is added it is OK all columns to be blank (except the store ID).

How to Add to MASTER and Populate Child.xlsx

Anyone know how to make this happen?

[Sandcastle]

Is bumping allowed?

[FDibbins]

Bit late to ask now LOL, but yes it is

[FDibbins]

The data in both sheets is the same. How do you determine which data goes to which sheet?

[Sandcastle]

Bit late to ask now LOL, but yes it is

I've been slapped down a few times so I've learned that the lightning speed of moderation is a useful tool in my idiocracy.

The data in both sheets is the same. How do you determine which data goes to which sheet?

The MASTER list has the primary concern of store number. The other lists only need to pull that store number whenever a new one is added. The rest of the new row can be blank because that data will be added through other processes.

[Sandcastle]

So, I've been trying all sorts of ways to make this happen, but the real solution has yet to be found.

Hoping that someone out there can help

[FlameRetired]

One way to do this is with a Dynamic Named Range (DNR). For example in Name Manager create a DNR called

MasterStoreList_DNR (the name is arbitrary and descriptive). Copy and paste this formula into the 'Refers to:' dialogue box.

Please Login or Register  to view this content.

Then in each of your child sheets in the first row of 'Store ID' type

Please Login or Register  to view this content.

and fill down as far enough to capture all the new store IDs and perhaps a bit further.

As you add to the Master table IDs the child tables will populate down as far as you filled down. The formula above

is limited to row 200 or 198 entries in your 'Master List'. I picked a number that seemed arbitrarily large enough as an upper boundary.

If you need to increase that simply edit the formula in Name Manager to reflect a larger upper boundary.......say 500 by

replacing the 200 with 500. Fill down the child columns far enough to capture all the new store IDs.