can vlookup place results in a different box than the formula?

If i write a vlookup formula in cell F1, can I somehow have the result show
up in f2. Also, can I have the result be a value. My need is to generate
monthly reports based on a separate Excel file and send them to someone.
When you separate the report from the original file, "help" pops up asking if
you want to update the file and that is a nusisance.

If possible, please reply to psychlogic@aol.com

thanks

First, you would need a formula in F2 (or use VBA) to have results show up in
F2, for instance =F1
Second, you can change the vlookup formulas to values by doing copy
pastespecial values (you can do this in place.)

You are probably not looking for a VBA solution for the copy pastespecial
values, but I do this so often that I recorded a macro and then made some
very slight changes. Code follows: (actually now that I look at it, I
probably did not modify it after recording it.)

Sub PasteVal()
'
' PasteVal Macro
' Macro recorded 12/19/2005 by kevinv
'
' Keyboard Shortcut: Ctrl+Shift+V
'
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.Select
End Sub

--
Kevin Vaughn


"Psychlogic" wrote:

> If i write a vlookup formula in cell F1, can I somehow have the result show
> up in f2. Also, can I have the result be a value. My need is to generate
> monthly reports based on a separate Excel file and send them to someone.
> When you separate the report from the original file, "help" pops up asking if
> you want to update the file and that is a nusisance.
>
> If possible, please reply to psychlogic@aol.com
>
> thanks

There's a toolbar button for this. Looks like a clipboard with the number 12
on it.

Biff

"Kevin Vaughn" <KevinVaughn@discussions.microsoft.com> wrote in message
news:A8CAE40B-CBA8-45FF-95E3-2F65E18E4597@microsoft.com...
> First, you would need a formula in F2 (or use VBA) to have results show up
> in
> F2, for instance =F1
> Second, you can change the vlookup formulas to values by doing copy
> pastespecial values (you can do this in place.)
>
> You are probably not looking for a VBA solution for the copy pastespecial
> values, but I do this so often that I recorded a macro and then made some
> very slight changes. Code follows: (actually now that I look at it, I
> probably did not modify it after recording it.)
>
> Sub PasteVal()
> '
> ' PasteVal Macro
> ' Macro recorded 12/19/2005 by kevinv
> '
> ' Keyboard Shortcut: Ctrl+Shift+V
> '
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
> _
> False, Transpose:=False
> Application.CutCopyMode = False
> ActiveCell.Select
> End Sub
>
> --
> Kevin Vaughn
>
>
> "Psychlogic" wrote:
>
>> If i write a vlookup formula in cell F1, can I somehow have the result
>> show
>> up in f2. Also, can I have the result be a value. My need is to
>> generate
>> monthly reports based on a separate Excel file and send them to someone.
>> When you separate the report from the original file, "help" pops up
>> asking if
>> you want to update the file and that is a nusisance.
>>
>> If possible, please reply to psychlogic@aol.com
>>
>> thanks

Well, you're right. I found the toolbar button under edit when I went to
customize. However, I have now tried copying it to two different toolbars,
and in both cases, it starts out looking like it will work, but when I close
customize, the button is grayed, and does not work when I click it.

I just now tried quitting excel and then restarting and it is still greyed
out. Looks like a pretty useful button, but it is not working for me.

--
Kevin Vaughn


"Biff" wrote:

> There's a toolbar button for this. Looks like a clipboard with the number 12
> on it.
>
> Biff
>
> "Kevin Vaughn" <KevinVaughn@discussions.microsoft.com> wrote in message
> news:A8CAE40B-CBA8-45FF-95E3-2F65E18E4597@microsoft.com...
> > First, you would need a formula in F2 (or use VBA) to have results show up
> > in
> > F2, for instance =F1
> > Second, you can change the vlookup formulas to values by doing copy
> > pastespecial values (you can do this in place.)
> >
> > You are probably not looking for a VBA solution for the copy pastespecial
> > values, but I do this so often that I recorded a macro and then made some
> > very slight changes. Code follows: (actually now that I look at it, I
> > probably did not modify it after recording it.)
> >
> > Sub PasteVal()
> > '
> > ' PasteVal Macro
> > ' Macro recorded 12/19/2005 by kevinv
> > '
> > ' Keyboard Shortcut: Ctrl+Shift+V
> > '
> > Selection.Copy
> > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
> > _
> > False, Transpose:=False
> > Application.CutCopyMode = False
> > ActiveCell.Select
> > End Sub
> >
> > --
> > Kevin Vaughn
> >
> >
> > "Psychlogic" wrote:
> >
> >> If i write a vlookup formula in cell F1, can I somehow have the result
> >> show
> >> up in f2. Also, can I have the result be a value. My need is to
> >> generate
> >> monthly reports based on a separate Excel file and send them to someone.
> >> When you separate the report from the original file, "help" pops up
> >> asking if
> >> you want to update the file and that is a nusisance.
> >>
> >> If possible, please reply to psychlogic@aol.com
> >>
> >> thanks
>
>
>

Oops, re my previous post. Just did a google search and found that it is
greyed out until you do a copy. Tried it and it works. Thanks!
--
Kevin Vaughn


"Biff" wrote:

> There's a toolbar button for this. Looks like a clipboard with the number 12
> on it.
>
> Biff
>
> "Kevin Vaughn" <KevinVaughn@discussions.microsoft.com> wrote in message
> news:A8CAE40B-CBA8-45FF-95E3-2F65E18E4597@microsoft.com...
> > First, you would need a formula in F2 (or use VBA) to have results show up
> > in
> > F2, for instance =F1
> > Second, you can change the vlookup formulas to values by doing copy
> > pastespecial values (you can do this in place.)
> >
> > You are probably not looking for a VBA solution for the copy pastespecial
> > values, but I do this so often that I recorded a macro and then made some
> > very slight changes. Code follows: (actually now that I look at it, I
> > probably did not modify it after recording it.)
> >
> > Sub PasteVal()
> > '
> > ' PasteVal Macro
> > ' Macro recorded 12/19/2005 by kevinv
> > '
> > ' Keyboard Shortcut: Ctrl+Shift+V
> > '
> > Selection.Copy
> > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
> > _
> > False, Transpose:=False
> > Application.CutCopyMode = False
> > ActiveCell.Select
> > End Sub
> >
> > --
> > Kevin Vaughn
> >
> >
> > "Psychlogic" wrote:
> >
> >> If i write a vlookup formula in cell F1, can I somehow have the result
> >> show
> >> up in f2. Also, can I have the result be a value. My need is to
> >> generate
> >> monthly reports based on a separate Excel file and send them to someone.
> >> When you separate the report from the original file, "help" pops up
> >> asking if
> >> you want to update the file and that is a nusisance.
> >>
> >> If possible, please reply to psychlogic@aol.com
> >>
> >> thanks
>
>
>

You could enter:
=F1
into F2, but that wouldn't solve your problem of separating the data from
the formulas when you copy and send the sheet elsewhere.

Try this:
Take a *copy* of the sheet,
<Ctrl> A
Right click in the selection and choose <Copy>,
Right click *again* and click on "Paste Special",
Click on "Values",
Then <OK> and <Esc>

And you have a sheet without any formulas, so that nothing will change or
update.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Psychlogic" <Psychlogic@discussions.microsoft.com> wrote in message
news:1B60F40D-A598-422D-A475-DD6E3D3C0BE4@microsoft.com...
> If i write a vlookup formula in cell F1, can I somehow have the result
show
> up in f2. Also, can I have the result be a value. My need is to generate
> monthly reports based on a separate Excel file and send them to someone.
> When you separate the report from the original file, "help" pops up asking
if
> you want to update the file and that is a nusisance.
>
> If possible, please reply to psychlogic@aol.com
>
> thanks

P:

If the issue you are trying to avoid is just the nagging box to update the
link, just break the link BEFORE you send the sheet.

1. With the sheet to be copied open, copy the sheet:
a. Right-click on the sheet TAB and select "Move or Copy..."
b. Check "Create a Copy" and select To Book: (new book).

That will create a new workbook with the desired spreadsheet.

2. Save the new workbook.
3. Now break the links to the old workbook.
a. From the menu, select Edit, Lnks...
b. Select the source link.
c. Click on Break Link, Break Links, Close.

Voila! Breaking the link(s) does NOT affect the cell value. The formula
containing the link is replaced with the last known value, which is, I
believe, exactly what you want.

4. Send the new workbook as an attachment.
a. From the menu, select Send To, Recipient (as attachment).

If you are NOT using Excel 2003, download and install Findlink.xla by Excel
MVP Bill Manville to find and break the links before sending.

HTH,

=Mac=

L. A. McClelland



"Psychlogic" <Psychlogic@discussions.microsoft.com> wrote in message
news:1B60F40D-A598-422D-A475-DD6E3D3C0BE4@microsoft.com...
> If i write a vlookup formula in cell F1, can I somehow have the result
> show
> up in f2. Also, can I have the result be a value. My need is to generate
> monthly reports based on a separate Excel file and send them to someone.
> When you separate the report from the original file, "help" pops up asking
> if
> you want to update the file and that is a nusisance.
>
> If possible, please reply to psychlogic@aol.com
>
> thanks