Tricky question about pasting references

[ModelerGirl]

I have a group of cells that are right next to each other. Call these,
A1-D1.
The cells reference cells in another sheet, but the cells they are
referencing are not necessarily adjacent. For example, A1-D1 will
contain (in order) references to Worsheet!C3,Worsheet!D3, Worksheet!D3,
and Worksheet!F3.

I'd like to start a new worksheet where the arrangement of the
references is maintained, except that I am now referencing a different
worksheet where the referenced cells are moved to the right.

For example, cells C3, D3, D3 and E3 are now NewWorksheet!E3,
NewWorksheet!F3, NewWorksheet!F3, and NewWorksheet!H3.

How do I do this?? Thanks a million,

Katie

 

[Herbert Seidenberg]

Assume the simplified data looks like this and
is located on the same sheet:
A B C D E F G H
19 20 21 22 23 24 25 26

21 22 22 24
$C$3 $D$3 $D$3 $F$3
C$3 D$3 D$3 F$3

23 24 24 26
E$3 F$3 F$3 H$3
$E$3 $F$3 $F$3 $H$3

Go to the cell with the formula =$C$3
press F2, then F4. Repeat for the rest of the row.
The formula in the first cell should now be =C$3.
Select the 4 cells you just changed and copy them
to a different row and two columns to the right.
The number data should have changed as shown above.
Go to the cell with the formula =E$3,
press F2, then F4 three times until the formula says =$E$3.
Repeat for the rest of the row.
You can now copy these cells to another sheet.
There are tools that make relative/absolute reference conversion easy:
PUP v6 at www.j-walk.com
or search for VBA code at these excel.* sites.