Import External Data

[matthewR]

I have a query in Access that is linked to a spreadsheet in Excel.
The query in Access is set up with the same columns as another spreadsheet in Excel.
What I am currently doing is, I have the spreadsheet that is linked to an Access query and then I have a spreadsheet that is set up by Manager.
This spreadsheet has totals and other formatting so I just copy what is needed from the Access query spreadsheet.
I put a + in the first cell of the first manager area, then I go to the Access query spreadsheet and highlight the first cell and hit enter which takes me back to the formatted spreadsheet.
Then I just copy over and down as far as the manager info goes.
I do this for each of the managers.

I just wondered if there was a more efficient way to do this. I have to do this mthly. The Access query will change every mth - shrink or expand. Any ideas would be appreciated.

[HansV]

Welcome to Eileen's Lounge!

Why do you have to repeat this each month? Once you've set up the formulas, they will update automatically. Or am I missing an essential point?

[matthewR]

The data could grow or shrink every month. I have to get rid of the excess rows if there is less data and add more rows if the data grows.

[HansV]

Where are the data entered? In Access or in Excel? That's not entirely clear to me from your description.

[matthewR]

The data is entered in Excel. I have an Access query that I use the "Import External Data" under the Data tab. Once this data is imported into Excel it is linked to the query in Excel. All I do when the query in Access changes is refresh the data in Excel. I then have another spreadsheet that is formated (meaning for every manager there is a total line and additional columns that are set up. I just have about 6 columns that I have to refresh for each manager. This formatted spreadsheet was set up by another person. She wants me to just enter the data around all her preformatting. I created the data (with corresponding columns) in Access to match the columns that I have to refresh. I sort it by manager name and then I just take each managers data and fill in her spreadsheet.

I just thought maybe there would be a more automated way to do this like with Offset etc.

[HansV]

I'm sorry, but your description is ambiguous. You write both "The data is entered in Excel" and "this data is imported into Excel". Which of the two is it?

[matthewR]

There are two spreadsheets - One spreadsheet is imported into Excel - I will call that spreadsheet 1. That spreadsheet has the updated information. The other spreadsheet (spreadsheet 2), has areas (by manager) where I fill in the data from Spreadsheet 1.

I setup the Access query (spreadsheet 1) to have the same columns as spreadsheet 2.

I have attached a spreadsheet with 2 tabs. One is the imported query from Access and the other is the spreadsheet I fill in with data from that query. I only fill in the data in columns A-H.

[HansV]

Ah, it's becoming clearer now, thank you. You could achieve the output in the second worksheet in the form of a report in Access based on the query, grouped by RVP. You could format that the way you want. Would that be feasible?

[matthewR]

I have to use the spreadsheet (spreadsheet 2) that is provided me. She has columns that she added that have calculations. The example I sent doesn't have all her calculations.

[HansV]

You could reproduce the calculations in the Access report.

Doing this in Excel is going to be nasty.

[matthewR]

What I am doing now works so maybe this is the best way. Some of her calculations are after the fact that she does to adjust the data so I don't think I can reproduce those in Access.

[HansV]

In that case, it might be best to continue as now.