Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Copy VLookup formula from one spreadsheet to another

I am by no means an excel guru, and I'm needing a little help. I have
numerous cells that are using the following Vlookup formula:
VLOOKUP("Total Amount ("&LEFT($C4,4)&")",Report!$B$1:$E$1000,2,FALSE) )

The "Report" sheet will be present in all subsequent workbooks that I
create. Whenever I copy and paste the formula to another workbook,
Excel places the name of the file in the formula. I.E.:

VLOOKUP("Total Amount ("&LEFT($C15,4)&")",'[Week 14.xls]Report'!$B$1:$E
$1000,2,FALSE)

How can I prevent it from doing this, or is there something that I can
put in the vlookup to tell it to always use the current workbook?

Thanks in advance.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Copy VLookup formula from one spreadsheet to another

You should Copy the Formula to Notepad and then copy (from Notepad) to the
second sheet to prevent it referencing the sheet it originally came from.

HTH

" wrote:

I am by no means an excel guru, and I'm needing a little help. I have
numerous cells that are using the following Vlookup formula:
VLOOKUP("Total Amount ("&LEFT($C4,4)&")",Report!$B$1:$E$1000,2,FALSE) )

The "Report" sheet will be present in all subsequent workbooks that I
create. Whenever I copy and paste the formula to another workbook,
Excel places the name of the file in the formula. I.E.:

VLOOKUP("Total Amount ("&LEFT($C15,4)&")",'[Week 14.xls]Report'!$B$1:$E
$1000,2,FALSE)

How can I prevent it from doing this, or is there something that I can
put in the vlookup to tell it to always use the current workbook?

Thanks in advance.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,268
Default Copy VLookup formula from one spreadsheet to another

Copy the formula from the formula bar or if there are many, replace the
equals sign with something unique like


find what =

replace with ^=^

then copy over the text strings to the new workbook, then reverse the
replace in both workbooks

--
Regards,

Peo Sjoblom


wrote in message
ups.com...
I am by no means an excel guru, and I'm needing a little help. I have
numerous cells that are using the following Vlookup formula:
VLOOKUP("Total Amount ("&LEFT($C4,4)&")",Report!$B$1:$E$1000,2,FALSE) )

The "Report" sheet will be present in all subsequent workbooks that I
create. Whenever I copy and paste the formula to another workbook,
Excel places the name of the file in the formula. I.E.:

VLOOKUP("Total Amount ("&LEFT($C15,4)&")",'[Week 14.xls]Report'!$B$1:$E
$1000,2,FALSE)

How can I prevent it from doing this, or is there something that I can
put in the vlookup to tell it to always use the current workbook?

Thanks in advance.



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default Copy VLookup formula from one spreadsheet to another

Thanks for the suggestions.

I have found that if you go into Edit....Links....Change Source to the
current file, that this updates the formulas. I'd really like a way in
the function to tell it to always use the current workbook, but I
don't know if there is a way to do this or not.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Copy VLookup formula from one spreadsheet to another

On Saturday, 14 April 2007 at 00:42:08 UTC+8, wrote:
Thanks for the suggestions.
I have found that if you go into Edit....Links....Change Source to the
current file, that this updates the formulas. I'd really like a way in
the function to tell it to always use the current workbook, but I
don't know if there is a way to do this or not.

Great thanks. Very helpful.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to copy a vlookup formula without changing the "table_array" p not 2 excell Excel Worksheet Functions 4 December 29th 06 04:49 PM
How do I copy spreadsheet data as shown to another spreadsheet? trainer07 Excel Discussion (Misc queries) 2 August 7th 06 09:39 PM
In Excel I want to copy text from spreadsheet to spreadsheet Kris Excel Worksheet Functions 3 June 9th 06 07:58 PM
new copy of spreadsheet using old spreadsheet info. e_vandenbush Excel Discussion (Misc queries) 1 January 17th 06 03:28 AM
Copy VLOOKUP Formula EMarre Excel Discussion (Misc queries) 4 October 21st 05 04:05 PM


All times are GMT +1. The time now is 01:18 PM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"