# Excel: How can I decide which cell to apply to a formula, based on the runtime contents of another cell?August 30, 2009 3:23 PM   Subscribe

Excel: How can I decide which cell to apply to a formula, based on the runtime contents of another cell?

For example, let's say I have column A filled with a bunch of values. In any given cell of column B, I want to display the value of the adjacent A-cell minus the value of some other A cell, a bit further down the column. But I don't know exactly how far down the column when I'm designing the spreadsheet, so I have another cell, C1, which tells me how far down.

So, if C1 has value 1, then B1 should be A1-A2, B2 should be A2-A3, and B3 should be A3-A4.

But if C1 has value 10, then B1 should be A1-A11, B2 should be A2-A12, and B3 should be B3-B13.

I'm imagining something like setting B1 to "A1-CELLATADDRESS(A, 1+\$C\$1))".
posted by Flunkie to Computers & Internet (4 answers total)

Use the INDIRECT function, together with the ROW and/or COLUMN functions.

For example, in the example I gave above, set B1 to:

A1-INDIRECT("A"&(ROW()+\$C\$1))

posted by Flunkie at 3:39 PM on August 30, 2009

No idea what the official word is, but in this case, it's the only answer, and it appears to be correct, so why not? From what I can tell, the "best answer" thing isn't so much a karma thing as a way to indicate that the question has an answer.
posted by effbot at 3:46 PM on August 30, 2009

Yeah, that's what I figured, but I wanted to make sure. I'll mark it; if an admin tells me otherwise, or anybody points me to a policy saying it's against etiquette, I'll unmark it.
posted by Flunkie at 4:42 PM on August 30, 2009

I would mark it best answer if I could. Thanks, I didn't know about that function! I wouldn't worry about the etiquette in this case.
posted by defcom1 at 10:29 PM on August 30, 2009