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

**judasdac** · 05-26-2009, 10:48 AM

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

**NBVC** · 05-26-2009, 10:56 AM

Try, in B21:

Please Login or Register  to view this content.

copied across.

**judasdac** · 05-26-2009, 05:55 PM

That worked great and I understand what I need to do should I have something like this arise in the future. Thanks so much for the help and apologies for not putting the formula in the proper brackets.