3D reference a range of cells 3

[PaulBricker]

I export data from a database to an excel spreadsheet. The data always writes to the same range of cells (overwrites existing data). Now, my bosses want to set up the spreadsheet so it is formatted for appearances but when I export, any formatting I add to the spreadsheet is lost. My solution was to set up one sheet, named EBKD, that has all the formatting they want, export the data to a second sheet, named expEBKD, and reference the cells on the second sheet from the first. Because the range on my unformatted sheet (expEBKD)will always be the same, I can easily reference the same data over and over again without touching the formatted sheet (EBKD). This all works fine. My problem is I don't want to have to type in hundreds of references (=expEBKD!$C$2, =expEBKD!$D$2... =expEBKD!$Z$2) for all the cells. I would like to set up a range of references. Something like =expEBKD!$C$2:$Z$244 I just haven't been able to figure out how to do that.
Any suggestions would be appreciated.

Paul

 

[PHV]

Instead of push from access why not pull from excel with a QueryTable ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

Just lose the absolute references by getting rid of the [!]$[/!]s. If you type in [COLOR=blue white]=expEBKD!C2[/color], then you can drag down and over as far as needed. (To drag, hover over the bottom right corner of the cell until the cursor changes into a dark 'plus sign' (+), then click and drag.)

BTW, the easiest way to set a reference to another sheet is to just type [COLOR=blue white]=[/color], then select the sheet and click in the cell you want to refer to. This way, you never have to worry about misspelling a sheet name.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PaulBricker]

Thank you both. anotherhiggins solution was what I was looking for.
PH, I will look at the QueryTable as well. I was not aware of any option like that.
I don't work in Excel much, but am always please with the solutions I've gotten from this forum.

Paul

 

[SkipVought]

As PHV has suggested, pulling the data via MS Query is the way to go. Set up the query ONCE and then just Data/Refresh to get current query results.

The Data/Data Range Properties will allow you to retain formatting, sort and filter properties as well as other features that you might find helpful like propogating formulas enter in adjacent columns to the right AUTOMATICALLY down thru the data range.

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[PaulBricker]

I had a chance to check out importing the data thru Excel, and that does seem the sensible way with the setup I am using. The only caveat might be teaching my bosses to click Data/Refresh. Currently, anytime the staff enters new data and closes the form in Access, it refreshes the spreadsheet. Is there a way in Excel (something like the On Open event in an Access form) that would automatically do the same thing when they go into the spreadsheet?
As I said before, I don't spend much time in Excel and am very unfamiliar with running macros/procedures in that app.


Thanks again

Paul

 

[PHV]

Right click inside the QueryTable range and look at the properties (like automatic refresh on open).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Data/Data Range Properties

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[PaulBricker]

Good morning and thanks again for the input. It is both interesting and helpful. The properties sheet has a few options that will make the import do just what I'd like but I'm still running into one issue. I set the refresh on open property but when I open the spreadsheet, I keep getting a dialog box with two buttons. One to Enable Automatic Refresh and one to Disable Automatic Refresh. The way it is worded, it seems that you should only have to enable the auto refresh once and be done with it, but I keep getting the box everytime I open the spreadsheet. I tried adjusting my security levels to see if that made a difference, but even the Low setting didn't change anything.
Any thoughts?

Paul

 

[SkipVought]

You are prompted to enable automatic refresh when you open a workbook in Excel

Skip,

_{[red]Be Advised![/red] For those this winter, caught in Arctic-like calamity, be consoled...
Many are cold, but few are frozen!}

 

[PaulBricker]

That means I have to get the network guys involved

I appreciate all the suggestions but at this point I'm sticking with my first solution.
My fuzzy logic:
1. There were issues opening the Excel sheet when there were users in the database when I used the "refresh on open property".
2. Network guys don't want to get involved.
3. I know it will work without hiccups (ok, probably work) doing it my way.

I did appreciate the appropriateness (if that's a word) of doing it the other way, but I'm more comfortable inside the database structure, so for now, this is where I'll stay.

Thanks again everyone.

Paul