Working with Two Workbooks

[froggie99]

Ok this might take ma awhile to explain, but i hope i make sense. I have been working with two Workbooks and each of them have several worksheets. For the first workbook i enter the information, in the second work book i have the graphs and data that i pull from the first. When ever i insert a new row in the first workbook, it doesn't update the second workbook.
For example:
I work for an Underwriting company and our custmors are Title Insurance Companies. When we have a new Agent order a policy from us, i enter that information into a spreadsheet on workbook 1. I have been doing this sense the beginning of year, i have a new spreadsheet for each month in workbook 1. In workbook 2 i have graphs in new spreadsheets of all of our Agents by month. So everyday i go in a update workbook 1 information from the prevois day. It then updates the graphs in workbook 2. But for some reason when i go to add a new agent into workbook 1 it doesn't move the informaiton with the row, so then all of my graphs are off by one row.

Does this make any sense to anybody? If so, please help me figure a way to make it update itself without me having to go in and change it.

 

[venkat1926]

not completely clear to me.

I think you should use the source data for the graphs a DYNAMIC RANGE. name.

I shall give you a trivial experiment
fill entries A1 to A8
give a name to this (insert-name-define)
for e.g. the name is "rng"(no quotes)
in the refer to window type this

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
note the dollar signs.

now in that sheet hit control +G and type rng
the cells A1 to A8 will be highlighted
now add one to more entries e.g. a9,a10
now again contrl+G and see that A1 to A10 is highlighted

this called dynamic range name . Even if some rows are deleted it works.

this experiment would have given you some idea how to solve your problem.

following urls will be of help

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

http://www.contextures.com/xlDataVal02.html#Dynamic

http://peltiertech.com/Excel/Charts/index.html

greetings
venkat