Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kinda like a database - I cant believe others dont need to do th
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kinda like a database - I cant believe others dont need to d
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Kinda like a database - I can't believe others don't need to d
Welcome. Delighted it helped.
-- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:365 Subscribers:65 xdemechanik --- "Bob" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I don´t view the value right after six decimal place in Excel | Excel Discussion (Misc queries) | |||
Dont want member number to repeat in the same column. | Excel Discussion (Misc queries) | |||
Text to speech buttons don´t go active | Excel Discussion (Misc queries) | |||
Can´t get any result | New Users to Excel | |||
Don´t show zero values in Pivot Table | Excel Discussion (Misc queries) |