Automatic update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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.
 
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
 
Back
Top