Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 31st 09, 12:54 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 2
Default vlookup-cell range in formula changes when copied to another cell

I've sent up several "forms" on one worksheet. When I copy the "master form"
under another, the cell range in the vlookup formula changes. I need the
cell range in the formula to remain the same no matter where it is copied in
the worksheet. Any suggestions?

example: =VLOOKUP(B39,'Other Crystals & Findings'!A1:B225,2)

I need it to always remain A1:B225.


Thanks,
Pixiemom

  #2   Report Post  
Old March 31st 09, 01:03 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 225
Default vlookup-cell range in formula changes when copied to another cell

Use absolute references like below
=VLOOKUP(B39,'Other Crystals & Findings'!$A$1:$B$225,2)

Even better
Create a NAME (eg Lookup_Table) for the range 'Other Crystals &
Findings'!$A$1:$B$225 and use that... like
=VLOOKUP(B39,Lookup_Table,2)

"pixiemom" wrote:

I've sent up several "forms" on one worksheet. When I copy the "master form"
under another, the cell range in the vlookup formula changes. I need the
cell range in the formula to remain the same no matter where it is copied in
the worksheet. Any suggestions?

example: =VLOOKUP(B39,'Other Crystals & Findings'!A1:B225,2)

I need it to always remain A1:B225.


Thanks,
Pixiemom

  #3   Report Post  
Old April 1st 09, 12:16 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 2
Default vlookup-cell range in formula changes when copied to another c

Thank you so much! I knew there was an answer out there somewhere.

Thanks,
Pixiemom


"Sheeloo" wrote:

Use absolute references like below
=VLOOKUP(B39,'Other Crystals & Findings'!$A$1:$B$225,2)

Even better
Create a NAME (eg Lookup_Table) for the range 'Other Crystals &
Findings'!$A$1:$B$225 and use that... like
=VLOOKUP(B39,Lookup_Table,2)

"pixiemom" wrote:

I've sent up several "forms" on one worksheet. When I copy the "master form"
under another, the cell range in the vlookup formula changes. I need the
cell range in the formula to remain the same no matter where it is copied in
the worksheet. Any suggestions?

example: =VLOOKUP(B39,'Other Crystals & Findings'!A1:B225,2)

I need it to always remain A1:B225.


Thanks,
Pixiemom



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
stopping value(from cell) from increasing when vlookup copied down PBcorn Excel Discussion (Misc queries) 1 April 14th 08 07:41 PM
Formula when copied into a new cell doesn't recalculate spmu Excel Discussion (Misc queries) 1 October 9th 07 03:09 PM
Unwanted formula being copied to cell Gene Excel Discussion (Misc queries) 3 April 17th 07 06:02 PM
cell format changes when formula copied? Todd Excel Worksheet Functions 1 April 14th 06 09:33 PM
I get a 0 value when a formula is copied to another cell arleen a. Excel Discussion (Misc queries) 4 August 9th 05 12:02 AM


All times are GMT +1. The time now is 06:26 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017