• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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?
  • 2
1 Solution
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) ?
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now