Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .