Connecting a master sheet to other sheets within the same workbook

[~genevieve]

Hello,

I have been trying to figure this one simple question (or not so simple)
out. Is it possible to tie information contained on a master sheet within one
Excel workbook to a different sheet in the same workbook?

For example, I have a master list with both the year 2009 and 2010 in it and
I want to make it so that if information typed into this master list contains
either 2009 or 2010 it goes to that specific sheet. So far, I have been
filtering by year and manually cutting and pasting the information into the
respective sheets.

Is it possible to program Excel to automatically recognize this in the
workbook? If so, all I need is the terminology used for this command and I
can figure it out from there!

If anyone happens to know, I would greatly appreciate it!

Thanks

 

[trip_to_tokyo]

Hi Genevieve, yes, it is possible.

1. For example put 1 in cell A1 of Sheet1.

2. Do a CTRL-C to copy the info in cell A1.

3. Click in Sheet2.

4. Go to cell A1 (of Sheet2).

5. Home / Clipboard / Paste / Paste Link.

Anything that you update in Sheet1 cell A1 will now be automatically updated
in Sheet2 cell A1.

There is an example of the above at:-

www.pierrefondes.com

- second example down (with the words JANEASH in it). If you look at Sheet2
and Sheet3 of this Workbook anything that is updated in Sheet2 is
automatically updated in Sheet3.

If my comments have been helpful to you please click Yes.

Thanks!

 

[Stefi]

This is a simple macro recording of what you did manually (provided sheets
named "Master", "2009", "2010" exist).

Sub Macro1()
Sheets("Master").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="2009"
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("2009").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Master").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=1, Criteria1:="2010"
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("2010").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Master").Select
Application.CutCopyMode = False
Selection.AutoFilter
End Sub

Re-run it, e.g. via a hot key, as required!

Regards,
Stefi


„~genevieve†ezt írta:

 

[Dave Peterson]

Saved from a previous post.

Excel doesn't do this kind of thing very well.

If you make a typing mistake, you'll have to provide code to remove the new
entry from the wrong sheet.

Instead, I'd keep all the data in one sheet. Use autofilter or sort to show the
stuff I need.

But if I needed separate worksheets, I'd refresh them from scratch each time I
needed them.

You may want to look at how Ron de Bruin and Debra Dalgleish approached this
kind of thing:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Or:

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