Doing what if scenarios with multiple worksheets
 

General type how should I question.

I've got a workbook with a consolidated data sheet which I then split up
into individual sheets for each person (lots of lines on consolidated sheet)
by converting it into a Pivot Table and then showing detail on each person.

I'd like to let users do some what if stuff where they change data from one
person to another. They will be doing it from the individual worksheets
(most likely).

So how do I do it? The problems I currently see are
A. The consolidated data comes from an outside source and I can't change it
- this must always be an exact duplicate of the original data.
1. When I use the pivot table to populate the individual worksheets it
copies the data and not the cell references so any changes can't be reflected
back/forward.
2. If I force them to use the consolidated sheet, again it doesn't copy the
changes forward and to do the pivot table thing, I'd actually have to make
the change permanent (or so I've found trying the scenario feature).
3. The individual worksheets must be populated before any what if stuff is
done.
4. I've thought about doing cell references on the individual sheets to the
consolidated sheet, but I'm a little daunted by the volume of data (smallest
total lines 2500, largest over 8000 with 64 columns plus new data for some
calculations) using if...then stuff. I think there's a way I can do it using
arrays but I've never done anything that way before.

I'm not asking for you to solve my problem just point me in a general
direction, I don't want to spend the time doing a lot of if...then coding if
there's an easier way.

Any help would be appreciated.