automatic update of linked data

Topic

New Reply

I have a spreadsheet that is linked to another spreadsheet. The two spreadsheets have the same data fields. I just put a + sign in one of the cells of spreadsheet b and go to the other spreadsheet (spreadsheet a) and click on a cell and hit enter which takes me back to the other spreadsheet (b) where the cell contents from spreadsheet a appears. I then copy over and down the data from the other spreadsheet. Spreadsheet a is connected to an Access query and updates when the Access query changes by hitting refresh. My problem is that the data in spreadsheet a changes (expands or contracts) according to the Access query. The linking to spreadsheet b needs to expand or contract also.

Is there any way to use dynamic range names to cause these sections in spreadsheet b to expand or contract according to the data in spreadsheet a. Is there a way to use OFFSET to copy over the data? Any suggestions would be greatly appreciated. I really don’t think so but I thought maybe someone would have an idea.

Reply | Quote

Replies

The spreadsheet I am updating is a spreadsheet that is set up with calculations and headings etc. I have different queries for different sections of the spreadsheet. When I connect to the Access queries for the different sections, I am overwriting the existing data. If the existing data is larger or smaller than than the query that the spreadsheet section is linked to, the data comes into the spreadsheet but the existing rows stay and are not removed. The data currently is less than the previous data that is in the spreadsheet. I am not sure what it does if there are more rows that are currently there. The extra rows have to be deleted manually unless there is something I am missing.

Reply | Quote

Is there a reason why you cannot use the same query in spreadsheet b rather than linking via formulas to spreadsheet a?

Reply | Quote

Yes, the spreadsheet I am linking to is premade . It is set up with calculations, headings and such. I am just updating certain sections with data that changes (monthly). I have linked the data that changes to the other worksheet. I have to put the data between the headings and calculations. The report has breaks between sections. I just fill in the sections with the new data. The problem is that the data needs to expand when there are more rows and contract when there are less rows.

I thought dynamic ranges would work but not sure how to use it in this scenario. If I name the different sections, how would I connect to the other worksheet. Right now what I do is refresh the original spreadsheet that is connected to an Access database (that works fine). I remove each sections data and then I put + and go to the other spreadsheet and select the first cell of the data I want to link to the other sheet, hit enter which takes me back to the other spreadsheet. I copy that cell over and down until the data changes to the next Representative. I then either delete extra rows or insert more rows to accomodate if there is extra data. I do this for each Representative.

I was hoping there was a better way to do this. Thanks for any help you can provide.

Reply | Quote

…
I was hoping there was a better way to do this. Thanks for any help you can provide.

How many “Representatives” are there, and are they all co-located? Also is this related to your other thread Database Query, and are you and linlorrie both working on the same issue?

Reply | Quote

Yes, I switched from trying to connect the data using Access. I use Access just to update the first spreadsheet. From that spreadsheet I populate the premade spreadsheet with the breaks, headings and calculations. The representatives could vary from month to month. I leave their section headings and calculations until the next month when they may or may not have data.

I hate to ask this in this discussion but how do I change a formula to accomodate for the missing data. Right now I get #REF! where the formulas are that do not have data – =SUM(E5,E7,E9,E13,E23,E27). I know there is some way to address this in the calculation.

Reply | Quote

I figured out how to prevent the #REF! error from showing. Just in case someone else has this problem.

=IF(ISERROR(SUM(E5,E7,E9,E13,E23,E27)),” “,SUM(E5,E7,E9,E13,E23,E27))

Reply | Quote