Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a simple, oft-used formula that I paste as plain text into spreadsheets I receive. It looks something like this:

=IF(D8="FOO", "BAR", "BAZ")

It looks in D8 for the word "FOO". If it finds it it will show "BAR". If it doesn't it will show "BAZ"

It works great. The problem is I have to paste this formula as plain text into many spreadsheets. It should ALWAYS look in column D for "FOO", however I don't always want it to look in row 8. I'd like it to look at whatever row I'm pasting it into.

For example, if I pasted the above formula into row 25, say, I would like it to automatically change to this:

=IF(D25="FOO", "BAR", "BAZ")

Is there any way to achieve this?

share|improve this question

2 Answers 2

up vote 5 down vote accepted
=IF(INDIRECT("D"&ROW())="FOO","BAR","BAZ")

That should work. INDIRECT isn't a very quick formula, so if you have a lot of them it could slow things down for you.

share|improve this answer
    
Worked perfectly, thanks very much. –  michaelmichael Jan 18 '11 at 15:17

Yes, There is a lot of details here. You can make cells reference other cells based upon where they are. My link is basically a how to for cell references.

share|improve this answer
    
I guess my question wasn't clear (or I'm not reading the contents of your link clearly enough). I get the concept of absolute vs. relative references, but the linked article isn't valid if I paste the formula as plain-text into another spreadsheet, say. I'm wondering if it's possible to write a formula that uses an absolute column reference but a row reference that's relative to the row the formula is pasted into. –  michaelmichael Jan 17 '11 at 22:27
    
Before reading this comment, I would like to answer you =IF($D8="FOO", "BAR", "BAZ"). I guess it is better to update this requirement to the question. –  wilson Jan 18 '11 at 1:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.