Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Master data seen on 3 tabs.
I have a long list of people by location, Fname, Lname, age:
Ohio, Tom, Smith, 12 Indiana, John, Johnson, 5 Ohio, Erica, Thompson, 40 Arizona, James, Simpson, 24 The list is currently in excel and I am frequently adding people to the list and would like to keep the master list while being able to see each location on a separate tab. So I want an Ohio tab, an Indiana tab, and a Arizona tab. Is there an equation or other way so I only have to add this information to the master tab and can automatically see it reflected in the state tab. I am trying to avoid doing double entry workand using Access. Each state tab has a slightly different format so using the filter won't work and I also need the master list. Thanks in advance for the solution. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Master data seen on 3 tabs.
Keep your data in one worksheet.
Then use data|filter|autofilter to see the states that you want. But if you really, really must. Keep the data in one worksheet and refresh the states worksheets whenever you need (not while doing input--just run the macro when you're ready). Ron de Bruin's EasyFilter addin: http://www.rondebruin.nl/easyfilter.htm Code from Debra Dalgleish's site: http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm KurtB wrote: I have a long list of people by location, Fname, Lname, age: Ohio, Tom, Smith, 12 Indiana, John, Johnson, 5 Ohio, Erica, Thompson, 40 Arizona, James, Simpson, 24 The list is currently in excel and I am frequently adding people to the list and would like to keep the master list while being able to see each location on a separate tab. So I want an Ohio tab, an Indiana tab, and a Arizona tab. Is there an equation or other way so I only have to add this information to the master tab and can automatically see it reflected in the state tab. I am trying to avoid doing double entry workand using Access. Each state tab has a slightly different format so using the filter won't work and I also need the master list. Thanks in advance for the solution. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Master data seen on 3 tabs.
Perhaps an alternative play using a non-array formulas set-up ..
Take this sample construct from my archives: http://www.savefile.com/files/430142 AutoCopy Lines to Resp Sht Non Array.xls (Full details inside, nicely rendered. Easy to adapt ..) Data is continuously entered in a master ("parent") sheet, with lines neatly auto-copied to each individual ("child") sheet based on the values within a key col. In the sample, the key col in the master sheet is the "State" col, which may contain eg: NY, CA, NV, SD, AZ, etc. All lines with "NY" in the key col will be auto-copied to the sheet named: NY, and appear neatly bunched at the top, w/o any intervening blank lines. Ditto for lines with "CA", "NV", etc which will be copied into their respective sheets. Propagation of the "child" sheet is as simple as making a copy of the initial one, then renaming it accordingly as the next key col value. Eg we first formulate one child sheet for "NY", dress it up nicely, then just make copies of the "NY" sheet, and rename these as: CA, NV, SD, etc. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "KurtB" wrote in message oups.com... I have a long list of people by location, Fname, Lname, age: Ohio, Tom, Smith, 12 Indiana, John, Johnson, 5 Ohio, Erica, Thompson, 40 Arizona, James, Simpson, 24 The list is currently in excel and I am frequently adding people to the list and would like to keep the master list while being able to see each location on a separate tab. So I want an Ohio tab, an Indiana tab, and a Arizona tab. Is there an equation or other way so I only have to add this information to the master tab and can automatically see it reflected in the state tab. I am trying to avoid doing double entry workand using Access. Each state tab has a slightly different format so using the filter won't work and I also need the master list. Thanks in advance for the solution. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto fill cells in other tabs from one master tab and protect them | Excel Worksheet Functions | |||
Updating tabs linked to master tab | Excel Discussion (Misc queries) | |||
Master spread sheet to manage then show info on seperate tabs | Excel Worksheet Functions | |||
Roll-up data into master report | Excel Discussion (Misc queries) | |||
combining tabs in one master tab | Excel Discussion (Misc queries) |