Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can vlookup place results in a different box than the formula?
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 thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can vlookup place results in a different box than the formula?
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" wrote in message ... 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 thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can vlookup place results in a different box than the formula?
There's a toolbar button for this. Looks like a clipboard with the number 12
on it. Biff "Kevin Vaughn" wrote in message ... 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 thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can vlookup place results in a different box than the formula?
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" wrote in message ... 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 thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can vlookup place results in a different box than the formula?
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" wrote in message ... 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 thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can vlookup place results in a different box than the formula?
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" wrote in message ... 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 thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup formula Excel version 2002 | Excel Discussion (Misc queries) | |||
Some formula results will not show up in the cell | Excel Worksheet Functions | |||
put formula results into a different cell if it is empty | Excel Worksheet Functions | |||
Cells displays formula, not results | Excel Worksheet Functions | |||
How do I use Range Names listed in a VLookup table in a formula? | Excel Worksheet Functions |