I realize this may not be a programming question, but...
I've got a workbook with several sheets. On sheet 2, I have several
columns with the following formulas:
=Sheet1!C$674
=Sheet1!C$684
=Sheet1!C$694 ... etc

I'm wanting to copy these to another location. They are in Column D
(sheet2) currently, and I'm wanting to copy them to another location on
sheet2; Column C.

However whenever I copy them, the formulas change to:
=Sheet1!B$674
=Sheet1!B$684
=Sheet1!B$694 ... etc

Is there anyway to get them to stay the same?!?

Thanks in advance...

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=379440

Put a "$" in front of the column as well, this makes the formula
absolute from a column and row perspective

--
tkstoc

-----------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...fo&userid=1444
View this thread: http://www.excelforum.com/showthread.php?threadid=37944

I understand that I can put a $ in front to make it absolute, but I
don't really want to have to go back in and type $ on every cell...

Any other alternatives??? Some sort of paste special?


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=379440

Just make a quick macro to do it for you:


Code
-------------------
set rng = selection
for each x in rng.cells
x.formula = left$(x.formula,9) & "$" & right(x.formula,len(x.formula)-9)
nex
-------------------


This code will add a $ for you (assuming the formulas start wit
"=Sheet1!")

HT

--
tkstoc

-----------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...fo&userid=1444
View this thread: http://www.excelforum.com/showthread.php?threadid=37944

Thanks tkstock, just a little shove in the right direction....


Thanks again for your help!!

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=379440

Actually, I just realized I miscounted - everywhere you see a 9, replac
with an 8.

Sorry about that..

--
tkstoc

-----------------------------------------------------------------------
tkstock's Profile: http://www.excelforum.com/member.php...fo&userid=1444
View this thread: http://www.excelforum.com/showthread.php?threadid=37944

Thats ok, the sheet was not named 'Sheet1', so I had to modify that
anyway.

Thanks again tkstock



DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=379440

Build this into code: sets the formula to column C

MsgBox Left(Selection.FormulaR1C1, (Len(Selection.FormulaR1C1) - 4)) & 3


Dim cel as Range

Dim cel As Range

For Each cel In Selection
If Len(cel.FormulaR1C1) 4 Then
cel.FormulaR1C1 = Left(cel.FormulaR1C1, (Len(cel.FormulaR1C1) - 4))
& 3
End If
Next





--
steveB

Remove "AYN" from email to respond
"DejaVu" wrote in
message ...

I realize this may not be a programming question, but...
I've got a workbook with several sheets. On sheet 2, I have several
columns with the following formulas:
=Sheet1!C$674
=Sheet1!C$684
=Sheet1!C$694 ... etc

I'm wanting to copy these to another location. They are in Column D
(sheet2) currently, and I'm wanting to copy them to another location on
sheet2; Column C.

However whenever I copy them, the formulas change to:
=Sheet1!B$674
=Sheet1!B$684
=Sheet1!B$694 ... etc

Is there anyway to get them to stay the same?!?

Thanks in advance...

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile:
http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=379440

I have a similar question and have already posted it.

have a spreadsheet that uses ODBC/MS Query to connect to a database and
pull in part numbers each time the worksheet is opened the file refreshes.
The part numbers are put in a column, and the contents from this column are
copied to another worksheet using the copy paste special option.

Each time the ODBC connects and refreshes the data the cell numbers change
and causes the links from source to target not to work. For example if I have
20 part numbers in cells C1:C20 in the source worksheet, which are copied to
the target worksheet when I created the orginial links, the next time the
file is opened/refreshed the cells in the source worksheet may no be C50:C70
or some other number the target worksheet is still expecting to copy C1:C20.
The part numbers in the target still show the orginial values when I created
the orginial links from cells C1:C20 in the source worksheet.

How can I fix this so the copy-paste-special links will always copy the
current data contained in the source worksheet regardless of the cell numbers?

"DejaVu" wrote:


I realize this may not be a programming question, but...
I've got a workbook with several sheets. On sheet 2, I have several
columns with the following formulas:
=Sheet1!C$674
=Sheet1!C$684
=Sheet1!C$694 ... etc

I'm wanting to copy these to another location. They are in Column D
(sheet2) currently, and I'm wanting to copy them to another location on
sheet2; Column C.

However whenever I copy them, the formulas change to:
=Sheet1!B$674
=Sheet1!B$684
=Sheet1!B$694 ... etc

Is there anyway to get them to stay the same?!?

Thanks in advance...

DejaVu


--
DejaVu
------------------------------------------------------------------------
DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629
View this thread: http://www.excelforum.com/showthread...hreadid=379440