Kinda like a database - I cant believe others dont need to do th
Sorry for the length but, Ive looked all over this sight and I cant find
anything close to what Im trying to do and it would seem to me to be a
common request.
I have 3 Forecast / Account Tracking workbooks (one for each sales manager)
with up to 15 worksheets (one for each salespersons forecast). The 15
sheets in the sales managers workbooks are all combined in a master
destination sheet via links. A pivot table is run against the destination
sheet for forecasting. Additional I have a worksheet with links that
consolidates the 3 sales managers combined sheets. I run a pivot table
against that sheet for a consolidated forecast.
Trouble is if you insert a row in the salespersons sheet the reference to
that row in the master disappears.
Example: sheet for salesperson 1 rows 2 €“ 5are linked to the master sheet
=salesperson1!A2 =salesperson1!B2 =salesperson1!C2
=salesperson1!A3 =salesperson1!B3 =salesperson1!C3
=salesperson1!A4 =salesperson1!B4 =salesperson1!C4
=salesperson1!A5 =salesperson1!B5 =salesperson1!C5
Insert a row on salesperson1 between row 2 and 3 €“ reference to row 3 on the
master is gone.
=salesperson1!A2 =salesperson1!B2 =salesperson1!C2
=salesperson1!A4 =salesperson1!B4 =salesperson1!C4
=salesperson1!A5 =salesperson1!B5 =salesperson1!C5
=salesperson1!A6 =salesperson1!B6 =salesperson1!C6
How can I have it keep the reference to Row 3 and create a row6
(=salesperson1!A6) as it did on the source sheet?
Kind of like a database.
Any help or guidance would be greatly appreciated.
|