#REF errors with updating database connections/Cell references changing

[Alistair Braidwood]

Good morning,

I'm currently designing a GM calculator for our company, using exported data (csv) from our orders system, then using a database connection to autoupdate the data when the spreadsheet is opened. Next to the data in the cells, I have 4 columns for working out the new/potential GM using a dropdown box of numbers 5-90, then calculating the price based on the data which has been imported.

I am having problems when changing the data to a different customer from our database software, as if there were 10 products, and the data now extends to 20 products, the GM calculator cell references, instead of carrying on say from A10, A11, change to A10, A20, A21. I have no idea why this would be happening apart from the data connection refreshing the data on open. Is there anyway I can make sure the calculation fields always point to the correct ones in another way than putting $A$1, $A$A2 as it's a very long winded way to do, as I have 4 tabs of data and potentially upto 100 products per customer.

Thanks in advance for any help.

Alistair

 

[Andrew Poulsom]

Welcome to MrExcel.

I don't think making the references absolute will help. That's only relevant when copying. You could try eg:

=INDEX(A:A,ROW(E1))

 

[Alistair Braidwood]

Welcome to MrExcel.

I don't think making the references absolute will help. That's only relevant when copying. You could try eg:

=INDEX(A:A,ROW(E1))

Thanks for the welcome.

Yes, the absolute references probably wouldn't work. I'm wondering if there's any kind of macro I could write, or settings I could change on the spreadsheet that would stop cell references changing from the correct formula to =(#REF!*100) / (100-H40) when the number of lines of data changes.

Can anyone shed any light on this frustrating problem?

Alistair.

 

[Andrew Poulsom]

Did you try what I suggested?

 

[Alistair Braidwood]

Yes, I've tried the above method, however, when I re-export the data from our database software, and refresh when opening the spreadsheet, for the lines that were not in use before, say after 10, the formula has changed to

=(INDEX(C:C,ROW(#REF!))*100)/(100-(INDEX(H:H,ROW(H17))))

 

[Andrew Poulsom]

For the ROW reference try using a cell in the same column as the formula.

 

[Alistair Braidwood]

Fantastic!

It looks like it's worked

Now, all I need is you to explain why I needed to use a row reference in the same column as the fomula for a bit of knowledge!

Thanks very much Andrew. You've solved a problem I've been working on for a good few hours and didn't look like I was going to solve it any time soon

Alistair

 

[Andrew Poulsom]

Now, all I need is you to explain why I needed to use a row reference in the same column as the fomula for a bit of knowledge!

Because cells in that column don't move when the external data is refreshed.