I have a sheet where a third of the data provided is from an Oracle, another third pulled from another sheet, and the rest are calculations based on the previous.

I am having an issue with one column in particular that s pulling data from another sheet. My Formula reads with the cell reference changing accordingly. When the table is refreshed it reverts back to the original formula and all cell references are lost.

I'm not sure I follow. I gather you have a workbook with at least two sheets; one I'll call Sheet1 and the other 'Current Status'. Somewhere on Sheet1, let's say in A1, you have this formula:It correctly displays the value from G2 in the Current Status sheet. But when something ("the table") is refreshed, a formula changes. Some specific questions:

1) You say the cell reference changes "accordingly"? What causes it to change? I'm guessing it's when you add, to Current-Status worksheet, rows above 2 or columns to the left of G; is that what you meant?
2) What, exactly, is refreshed? A range, a worksheet, the whole workbook?
3) How is it refreshed? By closing and opening the workbook? By hitting <F9>? What?
4) Which formula changes? The one in Sheet1!A1, I suppose? Or something else?
5) What does it change from? If it's Sheet1!A1, I suppose it changes from ='Current Status'!$G$2?
6) What does it change to?
7) Whatever it changes to, why do you refer to that as "the original formula"?

My applogies for not being as clear as I could have been. There are Two sheets in hte workbook

1)Current Status
2)History

The Current status sheet is a table of numbers that gets edited by hand with a set of numbers.

The History sheet is rather unqiue. It is a table where some of the data is pulled from an Oracle query. Some of the data is pulled in directly as it is entered on the "Current Status" sheet. Then there are some columns that are some simple arithmatic.


1) You say the cell reference changes "accordingly"? What causes it to change? I'm guessing it's when you add, to Current-Status worksheet, rows above 2 or columns to the left of G; is that what you meant?

Here I mean that there are 2 columns on the Current Status Sheet I am pulling data from (Column E and F). These then put into one Column on the History sheet. So I should see in column B on the history sheet
='Current Status'!$E$2
='Current Status'!$F$2
='Current Status'!$E$3
='Current Status'!$F$3

and so on

2) What, exactly, is refreshed? A range, a worksheet, the whole workbook?


When the workbook is opened the Oracle query on the history sheet is automatically refreshed to pull in the current data.

3) How is it refreshed? By closing and opening the workbook? By hitting <F9>? What?

It is an automatic refresh

4) Which formula changes? The one in Sheet1!A1, I suppose? Or something else?

The formula on the "history" sheet changes

5) What does it change from? If it's Sheet1!A1, I suppose it changes from ='Current Status'!$G$2?

='Current Status'!$G$2 is that it changes to. The correct formula is written above.

6) What does it change to?

See above


7) Whatever it changes to, why do you refer to that as "the original formula"?

Not sure on that one



Hope that helps.

Ok, I'm not with you yet but I'm making progress. I'll describe what I understand so far and you correct me:

In the History worksheet is a column that pulls data from another worksheet named Current Status. The formula for pulling that data is simple:...and so on.

But elsewhere on the History worksheet is a table that is populated from an Oracle query. Every time the workbook is opened the Oracle query is refreshed, which is as it should be — but when that happens, this column is changed, too, which shouldn't happen; every value is changed to "='Current Status'!$G$2". Your question is why the Oracle query is refreshing not just the proper range in the History worksheet, but also this column (and why just in that way). Right so far?

Actually, I'm pretty sure I'm wrong. Maybe the part that's getting updated is in the Oracle query's range. Or maybe not every formula in this column is being changed back to $G$2. Tell me what I have wrong and maybe we'll be ready to proceed from there.

You just about got it right except for the bit about the oracle query. The column that changes is a column that has been added to the table that draws from oracle.

The kicker here is that I have added other columns in this same exact fashion that are not having this issue.

In that case maybe I made a mistake by even trying. I don't use queries much—for that matter I don't use tables, either—so I probably can't help you. And yet by answering, I've given the impression to other casual readers that someone is already helping you with this question. Sorry about that . If no one else replies in the next day or two, maybe you'll have to repost it—and this time I'll stay out of it.

I thank you, Bob, for your attempt. I just miffed at how there is 1 column in this whole thing acting up. I want to just redo the column, but that would mean another 2 hours of reformatting and re-typing due to how the query works.....

I think maybe I gave up too soon. It's true that I don't know how queries interact with Excel tables, but the basic description leaves me puzzled. The way I'm picturing this ...

Look, let me make up some specifics. Your Oracle query is intended to update information within a certain range, right? So for the purpose of this question let's say that range is History!H2:K199; that is, when you refresh the Oracle query, it updates the contents of History!H2:K199.

Now your complaint, as I understand it, is that one of the columns in that range—let's say J2:J199—keeps changing. You set it to refer to the Current Status worksheet, as described below, but whenever the query is refreshed the formulae in that range change back to something else.

Here's my question: Since you say that J2:J199 is part of the table that the Oracle query updates, why are you surprised and upset that Oracle updates it? If you wanted the query to update that column, why is it a problem that it's doing so? And (here's the real question, I suspect), if you don't want the query to update that column, why is that column located within the table that the query updates? It seems to me that if you want this data to remain unchanged, then you must locate it somewhere outside the table that is being refreshed.