Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic update
I have a master spreadsheet that has 3 columns of data. The first column is
the Dept - lets say Dept A, B, C and D. There are 2 other columns of data. I would like to have separate spreadsheets for each department. Every time I enter information on the master spreadsheet, I would like (according to what Dept is entered) the data to automatically populate that department's spreadsheet. Is this possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic update
Unless you have a real requirement for doing it that way, it's probably more
trouble than it's worth...........you're better off with all the data in one database........if you want to see only one department at a time, use the Data Filter AutoFilter feature and select one department from the drop down arrow on that column......... Vaya con Dios, Chuck, CABGx3 "LLoraine" wrote: I have a master spreadsheet that has 3 columns of data. The first column is the Dept - lets say Dept A, B, C and D. There are 2 other columns of data. I would like to have separate spreadsheets for each department. Every time I enter information on the master spreadsheet, I would like (according to what Dept is entered) the data to automatically populate that department's spreadsheet. Is this possible? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic update
I read another post that is exactly what I want to do. They told her to do
the following : Hello all!! :) Is there an "if" formula (or any other) that will return an entire row? For example: Column A has my salespersons names Columns B - J have other info i have my "master list" of all contacts, and then I have a separate sheet for each of my salespeople. What formula would i use (if it is even possible) to have excel look for "Mike" on the master sheet & then return all the other info next to his name onto "Mike's sheet" each time it sees his name ? Let me know if i need to explain that more clearly! Thanks in advance for your time & help! VLOOKUP("mike",Sheet1!$A$1:$J$1000,2,FALSE) insert a column, B, number it 1-??, count the space over to the column you want it to pick up the info. if cell B13 has the number 3 in it, it will pick up that information in col. 3, so change the 2 in the formula to B?. VLOOKUP("mike",Sheet1!$A$1:$J$1000,B3,FALSE) copy and paste accross the page -- CBrausa ------------------------------------------------------------------------ Vlookup("mike",Sheet1!$a$1:$j$1000,3,false) will pull from column c Vlookup("mike",Sheet1!$a$1:$j$1000,4,false) will pull from column d Vlookup("mike",Sheet1!$a$1:$j$1000,5,false) will pull from column e Vlookup("mike",Sheet1!$a$1:$j$1000,6,false) will pull from column f Vlookup("mike",Sheet1!$a$1:$j$1000,7,false) will pull from column g etc... etc... But this is not dynamic. I put this in say Mike's spreadsheet. Like it says above but the first row is the only thing it pulls from the master spreadsheet. I try copying the formula down but it just copies the first row - It does not get any other data for Mike from the Master spreadsheet. If I enter something else for Mike, it doesn't automatically put the info on Mike's sheet. "CLR" wrote: Unless you have a real requirement for doing it that way, it's probably more trouble than it's worth...........you're better off with all the data in one database........if you want to see only one department at a time, use the Data Filter AutoFilter feature and select one department from the drop down arrow on that column......... Vaya con Dios, Chuck, CABGx3 "LLoraine" wrote: I have a master spreadsheet that has 3 columns of data. The first column is the Dept - lets say Dept A, B, C and D. There are 2 other columns of data. I would like to have separate spreadsheets for each department. Every time I enter information on the master spreadsheet, I would like (according to what Dept is entered) the data to automatically populate that department's spreadsheet. Is this possible? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic update
Personally, I wouldn't have anything this automatic. Too many things can go
wrong--starting with me typing the wrong name and never being able to find where it went. And I really try to keep all the data in one worksheet--then use data|filter to view the stuff I want. But if you need separate worksheets for each department, then I'd suggest using a macro and only running it when you're ready--not quite automatic, but pretty safe. Debra Dalgleish and Ron de Bruin have samples that you may like. Ron's addin may be sufficient right out of the box. Debra'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 or 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 And Ron de Bruin's easyfilter. http://www.rondebruin.nl/easyfilter.htm If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm LLoraine wrote: I have a master spreadsheet that has 3 columns of data. The first column is the Dept - lets say Dept A, B, C and D. There are 2 other columns of data. I would like to have separate spreadsheets for each department. Every time I enter information on the master spreadsheet, I would like (according to what Dept is entered) the data to automatically populate that department's spreadsheet. Is this possible? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
options to update automatic links | Excel Worksheet Functions | |||
Enable Automatic Update for Web Query | Excel Worksheet Functions | |||
Enable Automatic Update for Web Query | Excel Worksheet Functions | |||
Update Automatic Links Not Working in 2003 | Excel Discussion (Misc queries) | |||
Automatic Chart Update? | Charts and Charting in Excel |