Here's the problem:

I want excel to autofill a formula across a range of cells. This formula
has several logical tests in it, and all of these test are mutually
exclusive. When I autofill now, this formula changes the cell values for the
outputs (which I want), but it also changes the inputs for the logical tests
(which I don't). I want excel to lock the logical test cell references when
I autofill, but still change the true/false outputs as normal. More
concretely, if I entered this into A1 of worksheet3:

=(if'worksheet1'!A1,'worksheet2'!B1,'0')

How do I make excel do this:
=(if'worksheet1'!A1,'worksheet2'!B2,'0')

That is, keep A1 locked, but vary B2, instead of:
=(if'worksheet1'!A2,'worksheet2'!B2,'0')

when I autofill A2 of worksheet 3?

Hi,
Put a '$' in the cell reference you want to lock, i.e., $a$1.
--
RLA49


"Nathan" wrote:

Here's the problem:

I want excel to autofill a formula across a range of cells. This formula
has several logical tests in it, and all of these test are mutually
exclusive. When I autofill now, this formula changes the cell values for the
outputs (which I want), but it also changes the inputs for the logical tests
(which I don't). I want excel to lock the logical test cell references when
I autofill, but still change the true/false outputs as normal. More
concretely, if I entered this into A1 of worksheet3:

=(if'worksheet1'!A1,'worksheet2'!B1,'0')

How do I make excel do this:
=(if'worksheet1'!A1,'worksheet2'!B2,'0')

That is, keep A1 locked, but vary B2, instead of:
=(if'worksheet1'!A2,'worksheet2'!B2,'0')

when I autofill A2 of worksheet 3?