A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

vlookup-cell range in formula changes when copied to another cell



 
 
Thread Tools Display Modes
  #1  
Old March 31st 09, 12:54 AM posted to microsoft.public.excel.misc
pixiemom
external usenet poster
 
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
Ads
  #2  
Old March 31st 09, 01:03 AM posted to microsoft.public.excel.misc
Sheeloo[_4_]
external usenet poster
 
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  
Old April 1st 09, 12:16 AM posted to microsoft.public.excel.misc
pixiemom
external usenet poster
 
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 04:44 AM.


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