Thread: Paste formula across rows but change relative cell reference to column from row

Hi All,
I'm working on setting up a chart. The formula I'm using for cell B21 is this: =ROUND(F3,0) & " " & IF(E3<F3,"▲", IF(E3=F3,"●","▼")).

I'd like to able to copy this to cells C21 through H21 (along row 21), but when I copy it the formula becomes: =ROUND(G3,0) & " " & IF(F3<G3,"▲", IF(F3=G3,"●","▼"))

I'd actually like for the cells referenced in the above formula to be F4 and E4 and continuing in that order through H (i.e. the cells referenced in D21 would be F5 and E5, F6 and E6 in E21, etc.. I can't quite seem to figure this out though. Any ideas?

I would attach the worksheet, but I've been blocked from uploading files here at work. However, what I'm trying to accomplish is similar to what Chandoo has here.

Thanks in advance for your help.

Cheers
Tim

Try, in B21:

=ROUND(INDEX($F:$F,COLUMNS($A$1:C$1)),0) & " " & IF(INDEX($E:$E,COLUMNS($A$1:C$1))<INDEX($F:$F,COLUMNS($A$1:C$1)),"▲", IF(INDEX($E:$E,COLUMNS($A$1:C$1))=INDEX($F:$F,COLUMNS($A$1:C$1)),"●","▼"))

copied across.