formula cells being referenced keep chaning

Question

formula cells being referenced keep chaning

Anonymous

I am entering a formula on a second sheet to pull data from 2 cells on the first sheet. I need the cells referenced in the formula to stay the same even if i move the table of data in the first sheet but it wont. How do i fix this?

I have already turned off Extend data range formats and formulas

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

10 answers

Answer 1

HansV 462K MVP Volunteer Moderator

Let's say you want to refer to D4 on a sheet named My Sheet.

You can use

=INDIRECT("'My Sheet'!D4")

The formula will return the value of D4 even if you move stuff around, insert rows or columns, or delete them.

Answer 2

When i try that just to bring over one cell, not even trying to add the second cell it says:

theres a problem with the formula

Not trying to type a formula?

When the first character is an equal ('=') or minus ('-') sign, Ecvel thinks its a formula:

you type: =1+1, cell shows: 2

To get around this type an apostrophe (') first:

you type '=1+1, cell shows: =1+1

I am just trying to put in a formula on sheet two that pulls from fixed cells from sheet one and the formula should not change my cells i entered automatically when the data on sheet move moves around

Answer 3

HansV 462K MVP Volunteer Moderator

What is the formula you tried to enter but failed?

Answer 4

Anonymous

=INDIRECT("'Sheet1'!B3")+("'Sheet1'!D3")

I then just tried =INDIRECT("'Sheet1'!B3") to see if that would pull over just the one cell and it got the same error response

Answer 5

Anonymous

I kept trying the same formula with a different data set on sheet one. It is now in a #value! error on the cell on the second sheet. i am pulling the data from a picture if that makes a difference

Share via

formula cells being referenced keep chaning

10 answers