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

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 comments total)

Answering my own question:

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

Is it against etiquette to mark your own answer as "best answer"?
posted by Flunkie at 3:39 PM on August 30

Is it against etiquette to mark your own answer as "best answer"?

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

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

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