MAX, Perfect! Great! Awesome! I've spent hours trying to figure this out
and months living with a spreadsheet using straight links and always having
to clean up #REF errors.
Thank you!
Bob
"Max" wrote:
In your Master,
replace the top left cell where you have this link formula:
=salesperson1!A2
with this:
=OFFSET(salesperson1!$A$2,ROWS($1:1)-1,COLUMNS($A:A)-1)
Copy across / fill down as required to link it. Cover the max expected range
in the propagation. Test it out. The above will survive any new row
insertions/deletions in salesperson1 within the range covered, giving you
what you seek.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik
---
"Bob" wrote:
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!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.