cell reference "values only"

[Guest]

I am trying to transfer values from cells D1400 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.

 

[Guest]

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.

 

[S Davis]

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.





- 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.