Excel file doesn't retain references

[cuetzpalin]

Hello,

I'm using excel as my reporting tool. I use SQL to create tables based on user inputs. The tables are linked into an excel file. I have values from the table in a hidden worksheet. I use those values from the hidden worksheet to populate another sheet, which includes simple references from a cell in the hidden sheet to complex calculations based on values from the hidden sheet.

All the calculations/formulas work just fine. However, sometimes the reference gets thrown off.

For example:

Hidden sheet:

A2 = 100
A3 = Hello world
A4 = 5
A5 = 10
A6 = 15
A7 = 20
A8 = 25


User report sheet:

A2 = Hidden Sheet!A4
A3 = Hidden Sheet!A5
A4 = Hidden Sheet!A6
A5 = Hidden Sheet!A7

On some instances the reference from the user sheet get thrown off. They get bumped up a cell...like this:

User report sheet:

A2 = Hidden Sheet!A6
A3 = Hidden Sheet!A7
A4 = Hidden Sheet!A8
A5 = Hidden Sheet!A9

What could be causing this issue? Why doesn't the references stick?

Please help.

Thanks in advance!

Eddie

 

[SkipVought]

Hi,

Can users delete or insert rows?

Can the column be sorted?

Can cells be cut and pasted?

Skip,

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

 

[cuetzpalin]

Hi,

Yes, the user can manipulate all cells & rows in the user sheet. However, it's not a user error. When you first open the file you get the message asking whether or not you want enable or disable the auto-load of data from the linked datasource. After clicking "enable" you see all the refreshed data. At that time I'll see the broken references with the "#REF" error.

The Hidden Sheet never changes as far as rows are concerned. It's a static number of rows. Only the data in the row/cell changes.

Thanks,
Eddie

 

[SkipVought]

Check the Data > Data Range Properties - refresh type of the query table. In some cases data is replaced, in other cases inserted & deleted, which could cause your reference shift OR #REF loss of reference.

Skip,

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

 

[SkipVought]

I'd use the INDIRECT function to refer to the cells in question.

Skip,

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