excel cells don't keep formula in rows

I have an excel workbook with 2 work sheet.
sheet2 is populated with data from a query Import external data - edit query  from an access query.

sheet1 I have rows A filled from sheet2 rows b.

so row A1 in sheet1 is   sheet2!$B$1
            A2 in sheet1        sheet2!$B$2
so on ...

on first refresh sheet1 looks good, and sheet 2 always has the correct data.
On later refresh when sheet2 value changes and has correct data

A1 in sheet1 is   sheet2!$B$1
then I get REF
            A3 in sheet1        sheet2!$B$2
so on

so the rows gets pushed sometimes its like
A1 in sheet1 is   sheet2!$B$1
A3 in sheet1        sheet2!$B$17

I have tried everything by making  A1 Sheet2B1 and copy past the formula to rows below, but same result. It doesn't seem to stick with the formula that's why i tried
=sheet2$B$1(the row #)  but still doesn't stick. any idea why it would do this?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
Excel tries to track the data as the source rows move around. That's why your formulas are changing on you.

If you don't want that to happen, you might try a formula like:
=INDEX(Sheet2!B:B,ROW())            Put this formula in A1, then copy across and down

The INDEX formula in cell A1 will be pointing to Sheet2!B1. As you copy it down, it points to Sheet2!B2, Sheet2!B3, etc. If rows are deleted or inserted in Sheet2, your formulas will still be pointing to whatever has become Sheet2!B1, Sheet2!B2 and Sheet2!B3. In other words, you shouldn't be seeing #REF! error value unless Sheet2 also displays that error value.
SivasanAuthor Commented:
thank you,

so I should do INDEX(Sheet2!B:B,ROW())     where Row() would be the the row number so

INDEX(Sheet2!B:B,1) ?
byundtMechanical EngineerCommented:
ROW() is a function that returns the row number, ROW(W25) returns 25. When the parameter is omitted, ROW() returns the number of whatever row contains the formula.

So cells A1, A2 and A3 would have identical formulas:
but would return three different values, because the formulas are in three different rows.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.