share data and formatting between two sheets?

[Flagg89]

Hi All -

So, I'm familiar with sharing data between two sheets using a format like:
=sheet1!A1

However, what I'd like to be able to do is have two cells with the same data so that if I change either one, the other updates also. The value OR the formatting (say I change font color to red. etc.).

An example would be along the lines of a "master list" of customers on sheet1, with columns: Name, Product, Date, Paid (y/n)

On sheet2, I'd like to display a subset of this list, say only the customers with Name=Smith.

But, say on sheet2 I have Bob Smith who is not paid and I want to mark his record to update Paid to "y"... Is there a simple way to do this on sheet2 and have the data on sheet1 be updated? In this case, the data on sheet2 is being "pulled" from sheet1, but is there a way to "push" changes back to the sheet1 data?

I of course also want to be able to make changes to data on sheet1 and have it update on sheet2.

Also, I'd like to be able to apply bold or whatever while working in the subset on sheet2 and have that apply back to the "source" record in sheet1. And vice versa.

Any advice?

Thanks!

[MarvinP]

Hi Flagg89 and welcome to the forum,

I'm confused.
You want two sheets to look exactly the same in data and formatting. Is that right?

Why isn't one sheet good enough if they are going to be exactly alike?

[Flagg89]

Hey, thanks for the welcome.

So imagine this data set on sheet1.

Bill, Smith, widget1, 2011-06-01, y
Bob, Smith, widget1, 2011-06-02, n
Ed, Smith, widget2, 2011-06-02, n
Tom, Sanchez, widget3, 2011-07-31, y
Nick, Wilson, widget2, 2011-08-04, y

Sheet2 I would like to display a subset of this, say just "Smith" lastnames.

So:
Bill, Smith, widget1, 2011-06-01, y
Bob, Smith, widget1, 2011-06-02, n
Ed, Smith, widget2, 2011-06-02, n

I'd like to be able to edit data on sheet2 and have sheet1 also update with the changes. (or edit records on sheet1 and have sheet2 update)

Thanks for any assistance!

[MarvinP]

Hi Flagg89,

I'd still suggest a single sheet solution. Look at AutoFiltering and see if it doesn't do what you want. I've attached a sample but reading the links will also help.
http://www.contextures.com/xlautofilter01.html
http://www.timeatlas.com/5_Minute_Ti...ts_More_Useful

[Flagg89]

Hi MarvinP, thanks so much for the suggestion.

It doesn't exactly fit with what I was envisioning as the ideal solution, but will probably work for me.

I probably should have outlined the exact scenario I was trying to find a solution for, but I gave a non-real-world example. My thought was to provide an example that most people could relate to (ie the imaginary list of customers) so that future visitors to this thread would get the most benefit without necessarily getting bogged down in the minutiae of the "actual" example.

What I'm really doing is trying to come up with a spreadsheet to manage a fantasy football draft. What makes this a little different, I guess, than other applications of this scenario is that the draft itself will be a live event, with a time limit element. So, my thought was to have something that makes most efficient use of time.

So, when I referred to a "master list" above, what I actually have is a master list of players that can be selected during the draft. So along the lines of this on sheet1: PlayerName, Team, Position, ByeWeek, FantasyValue, Notes

There'd be maybe 200-300 records on this list.

Now, each group (or "league") has it's own rules and roster setup for these things. Ours for example allows 1 active quarterback, 1 "benched", 2 active running backs, 2 benched, and so on.

So my thought was to have on sheet2 several subsets displayed from the master list, filtered by Position and sorted by FantasyValue. So that one section shows a subset of say just Quarterbacks sorted descending by FantasyValue. Another section would show the Running Back subset, and so on. This gives me something of a "top down" view of all the players available, but organized visually by their position (as I need to fill only so many spots from each position).

During the draft, people take "turns" and pick their desired player from the list of who is still available. What I was thinking is trying to set it up so that no matter if I was reviewing the master list or one of the subsets, then I could apply a strikethrough to a player when someone selects it, and have that formatting carry over to both instances of that record, thus saving me from flipping back to the master list everytime and searching for that player from the entire list. Likewise, when it is my turn to select a player, and say I decide to pick a Kicker, it would be ideal to be looking at my Kicker subset, apply color:red right there and have that formatting mirrored on the sheet1 master list data.

All that said, your suggestion looks like it will be the simplest one.

The one downside is I cannot see all of my subsets on one screen: Quarterbacks, running backs, wide receivers, kickers, and defense. I'll need to reapply an autofilter everytime I want to view just the subset, and then un-apply it to review the master again. Not a big deal in a normal setting, but in a time constrained live situation, it could make a difference.

Thanks again for your help, very much appreciated!

[MarvinP]

Hi Flagg89

I saw a fantacy football team picker and evaluator a while back at:
http://www.excelforum.com/excel-prog...ml#post2588081

It seems to me you could tag each player with the Owner of that player and do Pivot Tables on the data to see who was ahead after the daily data had been updated.

Perhaps all the work already done on a similar problem will give you a better direction.