Copying formulas, relative and absolute cell references

[Guest]

I have a formula that refers to an absolute row of cells but the columns are
relative and change one at a time. I want to paste these formulas into
another worksheet where the formula is applied to every other cell in a
constant row.

Currently when I copy and paste the formulas they'll look something like
this:

Into Worksheet 2 A200, I want to insert this formula
=Worksheet 1 !A $203.

Into Worksheet 2 F200, I want to insert this formula
=Worksheet 1 !B$203

Right now I'm getting a relative column reference but its calculating the
column distance from Worksheet 2 cell references and incorporating those into
the copied formula

Is there any way to copy and paste the formulas in an easier manner than
jumping from cell to cell and changing the column reference in the copied
formula?

Thanks!

 

[Guest]

Can you create the formula in another area, then copy right as many times as
needed, then highlight the row, Edit/Find/Replace ! with !$ to make the
column absolute, then cut and paste the formulas into the needed places?

It's not pretty, but maybe easier..

 

[Guest]

Thanks for the response. I'll give that a try but it doesn't sound much
better than my current work around: I'm selecting the cells that I want to
copy then selecting the multiple destination cells in the other worksheet.
After Edit>Paste Special>Paste Formulas, I go to each cell and change the
column reference to the one I want.

 

[David Biddulph]

If you do mean every other cell, then try
=OFFSET('Worksheet 1'!$A$203,0,(COLUMN()-1)/2)

In your example you went from column A to column F in your destination
sheet, so if you want a shift of 5 columns in your destination sheet to give
a shift of 1 column in your source sheet, try
=OFFSET('Worksheet 1'!$A$203,0,(COLUMN()-1)/5)