cell reference from a cell reference

[drewnis]

I can't figure out how to ask this question so google will give me an answer. I've often wanted to do this. It doesn't matter why. Please don't be a troll and tell me why I'm wrong to want to do this.

Here's the formula I entered in A1:

=if($B1>1,"yep","no")


And then I pasted it into thirty additional cells below it. So I get a series of formulas that are automagically changed to yield this:

=if($B2>1,"yep","no")
=if($B3>1,"yep","no")
...
=if($B30>1,"yep","no")


I want to be able to replace the "$B1" in all those cells with "$C1" or "$D1" without having to rewrite the formula and paste it into all the cells below it over and over again.

So what I'd like to do is just type "$B1" in cell A32. Then I want the formula in A1 to automatically insert into the formula whatever I've typed in A32 and use that in the same way as if I'd literally typed it.

So instead this:
=if($B1>1,"yep","no")
I want this:
=if((=A32)>1,"yep","no")

But I just don't know the right command to get it to do that. "(=A32)" doesn't work. But there must be something that has that effect right?

Thank you so much for the help.

 

[gaz_chops]

The problem you have is that each cell is referencing a different row, so if you enter B1 in cell A32 then use

=IF(INDIRECT($A$32)>1,"yep","no")

Every cell will look at B1!

The way around this would be to enter B into A32 then use

=IF(INDIRECT($A$32&ROW())>1,"yep","no")
Which adds the row number to B, this will only work providing that the cell you want to reference is on the same row.

 

[drewnis]

Thanks, great! I didn't know about INDIRECT. Most helpful.

 

[gaz_chops]

You're welcome.