Can't make a cell absolute when referenced to another worksheet?

[jack.k]

Hi. I'm been searching for an answer to this problem for days, can someone please help me out. I would greatly appreciate it!

To keep it simple, this is what I'm basically trying to accomplish. I have two sheets, say Sheet1 and Sheet2.

On Sheet1 I have an IF statement. =IF(Sheet2!C5="OK","OK","-"). This works well, however, when I put a new row in Sheet2, it adjusts the C5 formula in my Sheet1 formula to C6.

I want the formula in Sheet1 to always stay referenced to cell C5 on Sheet2.

So I learned that I can use $ to make absolute references. But for some strange reason, Sheet2!$C$5 / Sheet2!C$5 / Sheet2!$C5 are all failing to accomplish this.

I don't understand what I'm doing wrong. Can someone please help me out. Thanks!

[Fotis1991]

Hi and welcome to the forum

This, must work for you.

=IF(Sheet2!$C$5="OK","OK","-")

[jack.k]

Hi Fotis, thanks for the welcome!

Thank you for the sheet example, but it still does not work. If you go into Sheet2 and put a new row anywhere above, and go back to Sheet1, the formula changes to =IF(Sheet2!$C$6="OK","OK","-")

[Cutter]

Maybe this is what you want?

=IF(INDEX(Sheet2!C:C,5)="OK","OK","-")

[Fotis1991]

..Yes, you are right...

So try, this.

=IF(INDIRECT("Sheet2!C5")="OK","OK","-")

Is this, OK, for you?

[Firefly2012]

You could alternatively use INDIRECT:

=IF(INDIRECT("Sheet2!C5")="OK","OK","-")

[jack.k]

The formula using Indirect works! Thank you so very much!!

I never heard of Indirect before.

[Cutter]

INDIRECT() is a volatile function and, as such, shouldn't be used when non-volatile options are available.

[Fotis1991]

You are welcome.Glad to helped you.

I have to tell you, that also Cutter's formula, works great in this case.

[jack.k]

Actually, Cutter's formula using INDEX although confusing turned out to be better. With INDIRECT I kept on getting prompts to save the file when there weren't any changes. I guess this is what is meant by volatile. Thanks again for the help guys!

Edit: How do I mark a thread as solved?

[DGagnon]

To mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.