How do I keep the formula ranges from changing when I refresh linked Access Tables 2

[nicknick33]

I have created a workbook with several worksheets in it.

Some of the worksheets are linked to Access Tables and the other worksheets use these linked tables as the source of information. The formulas use ranges as well as mirror the linked tables with some additional columns that have calculations in them. Below are examples:

I have a worksheet called IB-NOPO-RateData and the following are the formulas in some of the columns:

='IB-FRT-NoPO-Q511'!A26
='IB-FRT-NoPO-Q511'!D26
etc etc

The sheet 'IB-FRT-NoPO-Q511' is ther linked table

Also, below is a formula in another sheet:
=SUMIF('IB-FRT-WPO-Q510'!$N$2:$N$4982,"<>UPS",'IB-FRT-WPO-Q510'!$O$2:$O$4982)+SUMIF('IB-FRT-NoPO-Q511'!$N$27:$N$4976,"<>UPS",'IB-FRT-NoPO-Q511'!$O$27:$O$4976)

'IB-FRT-WPO-Q510' and 'IB-FRT-NoPO-Q511' are linked tables

THE PROBLEM:
When I refresh the tables, these formulas change. For example:
='IB-FRT-NoPO-Q511'!D26 turns into ='IB-FRT-NoPO-Q511'!D29 or sometimes it returns #Ref! error

The other formula with the range in it ends up with a different range thus omitting some of the rows in it. For example, the above long formula turns into:
=SUMIF('IB-FRT-WPO-Q510'!$N$8:$N$5018,"<>UPS",'IB-FRT-WPO-Q510'!$O$8:$O$5018)+SUMIF('IB-FRT-NoPO-Q511'!$N$27:$N$4976,"<>UPS",'IB-FRT-NoPO-Q511'!$O$27:$O$4976)

This is driving me nuts and everytime I refresh, I have to go back and change the formulas. I refresh the sheet by right clicking a cell and selecting "Refresh"

In fact, I created a Macro that goes thru all the sheets executing the same procedure. There must be some configuration in the Connection properties that I should have but I am not sure what that should be. My current connection string is below:


Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\DC\Tuccini\BusinessFlow2.mdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False

Anyone has any ideas?

By the way, I am using Excel 2007.

Thank you so much in advance.

 

[SkipVought]

hi,

your approch to referencing data on another sheet it TOTALLY WRONG!!!

Your imported data ranges WILL CHANGE.

Your imported data WILL CHANGE.

Direct references will NEVER be adequate to that kind of dynamic situation in almost every instance.

Consequently, you must use indirect referencing techniques, many of which are native Excel features in LOOKUP fucntions and/or AGGREGATION fucntions. Other techniques are PivotTables and MS Query.

It all depends on exactly what you are attempting to do with your referencing sheet.

Please tell us WHY you have had your direct references and WHAT the purpose of these references is.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[nicknick33]

Thanks for the info Skip.

Basically I have billing data imported into a table in MS Access. I then have several querries set up to separate the data into groups.

I linked those queries into an excel sheet where I run calculations to audit the billing data.

For example, one table linked to a sheet brings in inbound shipment charges.
I then have a sheet that ready every shipment charge and looks up on another sheep thru look up what the rate should be and then calculates if there is overcharge or undercharge.

So at record level, I am doing calculations. The problem seems to be missing lines. For example the source data in the linked table sheet has row 1 thru 100 and the calculation sheet has row 1 thru 100. When I refresh, the calculation sheet rows get removed leaving the last row linked to the table but a number of them in between get removed.

 

[SkipVought]

So what does a reference on your sheet do for you?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[nicknick33]

It reads from the sheet that has the linked table. When it refreshes, the reference gets lost or replaced.

 

[SkipVought]

As I stated before, that is not a very good way to get data from that sheet.

What are you trying to do with the imported data besides just getting the data into another sheet?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's a hypothetical for purposes of discussion.

[b]
Flight  Gate[/b]
1685    A33
437     B22

Let's suppose that you were importing flights arriving within the next 60 minutes. So one time you might get 2 rows and at another time you might get 6 rows.

So what do you intend to do with the DATA, once you have it where you want to do something with it?

You could, for instance, tell use what YOUR data looks like and what YOU want to do with it. THAT information will help a helper determine WHAT to recommend as a solution, because YOU already have a solution in your head that obviously is not working, else you would not be asking for a solution. Trouble is, you still have the same solution in your head and cannot get around it, it appears.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's something else that I notice. If you were using the capability of the table attributes of the import table, assuming that you are importing the data using a QueryTable, then your TABLE has a NAME; lest's assume the Table Name is tIN

Furthermore, I notice that you are CHOOSING row ranges for each SUMIF. WHAT is that difference in rnage based on? Why 8:5018 and 27:4976???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

What is the data in column N and column O

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

I didn't read the entire thread, but do notice it looks like you are using Tables, where you've linked the data from Access, but you aren't using them to their potential.

Without going into great detail, here's what you need to do.

Let's say you have a table of fruit. In that table, you've got characteristics of fruit at columns.

So like: (I notice in preview that my table isn't lining up too well, even using TT, so just copy/paste to notepad or something of that sort to see it more clearly)
[TT]
FruitName FruitType FruitColor FruitSmell FruitWeight
Fuji Apple Red Sweet .5
GrannySmith Apple Green Sweet/Sour .5
Lemon Lemon Yellow Sour .2
Navel Orange Orange Sweet/Sour .6
[/TT]
And you want to reference the columns.

Well, first of all, make sure you give it a name you can work with and recognize, just like in a database.

To do so:
1. Make sure you have a cell selected within the given table.
2. Look on the MS Office Ribbon for Table Tools - Design tab, and make sure it's selected
3. On that ribbon, far-left side, you should see a text box that has the current table name. If you didn't specify a name for Excel, it may be "Table1"
4. Rename the table to what fits best

Now, you can easily refer to that table and its columns.

Example formulas:

Get the average weight of the fruit:
=AVERAGE(Fruit[Weight])

Get the heaviest fruit weight:
=MAX(Fruit[Weight])

Let us know if that works for you, or if it helps clear up anything at all..



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

nick,

kjv stated what I was headed to state in my previous post but got distracted.

We need to know more about your workbook structure and import tables.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

@ kjv1611,
Instead of usinh [tt], use [ignore][pre][/ignore]:

[ignore][pre][/ignore][pre]
FruitName FruitType FruitColor FruitSmell FruitWeight
Fuji Apple Red Sweet .5
GrannySmith Apple Green Sweet/Sour .5
Lemon Lemon Yellow Sour .2
Navel Orange Orange Sweet/Sour .6
[/pre][ignore][/pre][/ignore]

Have fun.

---- Andy

 

[SkipVought]

btw, it is actually extremely desirable for RANGE references to other tables to automatically change as soon as the tables change.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Thanks, Andy for pointing that one out!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

By the way... I can't find [ignore][pre[/ignore] anywhere in the tgmlinfo link.

Any way to see the full possible list of tgml codes?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[mscallisto]

kjv16611

to see the old tgml codes click the ? found to the right of the [submit post] button

sam

 

[mscallisto]

Oops sorry, I know the year was 1611.

 

[kjv1611]

Yeah, thanks (Yes, I realize a little late in responding ), mscallisto. I know about the help, but the [pre] tgml code is NOT listed in the help - check there to see. if it is, then for some reason it is not showing up whenever I look.

So is there any other reference that lists tmgml codes NOT listed in that "main" list?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57