Auto Populating Multiple Sheets

**babalurugby** · 05-03-2012, 01:39 PM

I have a spreadsheet with multiple pages and lots of data. This is how it is set up:
*Page 1= Total list of all the people, their organization, whether they are cleared on certain criteria or not (i.e. John Smith, 113th Cav, Dental Class 3, Not Clear)
*Page 2= List of all people who are not cleared or have deficiencies

Page 3-15 are unit breakdowns so it only lists the people in a certain unit

What I'm trying to do is if I put in information on page 1 that it will automatically populate on the corresponding pages. For example, if I put someone on the total list that is in the 113th Cav, he will automatically populate on the 113th Cav page.

Or if I change someone on the total list from Cleared to Not Clear, that he will automatically populate on the deficiencies page.

I was told you can't do that with formulas but would have to code, no idea how to do that.

Thanks so much EXAMPLE IMR.xlsx

**JBeaucaire** · 05-03-2012, 09:37 PM

You can do it with formulas, too. Which is your preference?

Formulas = larger file size, but everything happens in realtime and macros are not enabled

Macros = smaller file size, can happen in reatime, too, but macros would have to be enabled all the time on that workbook.

**babalurugby** · 05-07-2012, 08:55 AM

I would prefer to do it with formulas. But learning how to do it both ways wouldn't hurt I guess. Sorry for the late response, have been gone all weekend.

**JBeaucaire** · 05-07-2012, 09:41 AM

The main sheet now has two keys added to columns S:T to make simple indexes the other sheets can use to grab rows of data sequentially without using array formulas.

The Deficiencies page just has a key cell in S1 to collect one time the max number of rows needed to display. Then the formula in A2 is entered, then copied down and across the table for as many rows as you wish. I had to stop at 6 since you have totals below that. You'll have to work on your sheet design, it's not all that practical to have your totals at the bottom when you might need the data on the child sheets to expand down a ways on its own.

The Deficiencies page uses the key from column S on the Total List sheet. All the other sheets use key column T.

The other sheets use a unique formula in cell T1 that causes the sheet's name to appear, and thus can be used in a MATCH formula to grab only the key items that start with that text. The interesting thing about these sheets.... just duplicate an entire sheet (right-click the sheet tab and select COPY) and then change the tab name and it will instantly start working for that new name with no other edits.

I also went into the File > Options > Advanced > Worksheet and turned off the "display zeros" option for all the child sheets.