Kinda like a database - I can’t believe others don’t need to do th

[Bob]

Sorry for the length but, I’ve looked all over this sight and I can’t find
anything close to what I’m 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 salesperson’s 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 salesperson’s 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.

 

[Max]

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]

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]

Welcome. Delighted it helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:365 Subscribers:65
xdemechanik