I am trying to transfer values from cells D1:D400 in worksheet2(ws2) to cells
B1:B400 in worksheet1(ws1). I only want the values to appear in worksheet1
and not the cell reference formula. One method is to copy cells form ws 2 and
do edit paste special-values but I am wondering if there is a way to put a
formula in cell ws1 B1 such as =ws2D1 then copy that down to D400. Is there a
way to do this and get the values only and not the formula =ws2 D2. I have
many columns to do and I am trying to avoid having to switch back and forth
from the various worksheets. I appreciate any suggestions.

--
Shooter

By definition, a formula is a formula is a formula. The only way (short of
VBA) to convert it to a value is do as you described - Edit-Paste
special-Values.

"Shooter" wrote:

I am trying to transfer values from cells D1:D400 in worksheet2(ws2) to cells
B1:B400 in worksheet1(ws1). I only want the values to appear in worksheet1
and not the cell reference formula. One method is to copy cells form ws 2 and
do edit paste special-values but I am wondering if there is a way to put a
formula in cell ws1 B1 such as =ws2D1 then copy that down to D400. Is there a
way to do this and get the values only and not the formula =ws2 D2. I have
many columns to do and I am trying to avoid having to switch back and forth
from the various worksheets. I appreciate any suggestions.

--
Shooter

On Mar 15, 10:10 am, Duke Carey
wrote:
By definition, a formula is a formula is a formula. The only way (short of
VBA) to convert it to a value is do as you described - Edit-Paste
special-Values.



"Shooter" wrote:
I am trying to transfer values from cells D1:D400 in worksheet2(ws2) to cells
B1:B400 in worksheet1(ws1). I only want the values to appear in worksheet1
and not the cell reference formula. One method is to copy cells form ws 2 and
do edit paste special-values but I am wondering if there is a way to put a
formula in cell ws1 B1 such as =ws2D1 then copy that down to D400. Is there a
way to do this and get the values only and not the formula =ws2 D2. I have
many columns to do and I am trying to avoid having to switch back and forth
from the various worksheets. I appreciate any suggestions.

--
Shooter- Hide quoted text -

- Show quoted text -

Yeah, there's no way to just have values appear without a formula to
tell excel what you want to see... short of the copy - paste special -
values command. Or VBA, which would probably be more work than just
copying and pasting:)

You should try experiementing with the INDIRECT and OFFSET functions
though... Im busy here today, but I know that you can put sheet names
into a cell and then use indirect to call that sheet, then offset off
a cell within the sheet. The challenge will be finding a way to make
"Sheet2!A1" dynamic, so that when you scroll down it shifts to Sheet2!
A2.

Id work on this, but too much to do of real work right now:) Someone
out there should be able to help you.