The terror of advancing cell numbers!

Hi everybody,
I apologize for this really obvious problem, I haven't encountered i
before so its really ummm "bugging" me.

I am using the vlookup function, and in the formulae it refers to 
range of figures A1-Z100 in another worksheet. If you copy and past
the formulae to find the next piece of information xcel automaticall
advances the the references, so that now the range of figures it refer
to is A2-Z101, which means that the vlookup function is no longe
looking in the full range of figures. How do I stop it from doin
this?

In the past I just did one row, manually changing the table it refer
to, then copying and pasting that entire row of formulae to the next
and xcel would get the idea that I didn't want the cell numbers t
advance. But I was using a different version of xcel which i
remarkably insistent in its cell advancing afforts. To avoid this 
manually did two rows of formulae, setting the table array manually an
then copying and pasting both rows. Instead of sticking to the tabl
array as it should have done, the bloody thing advanced the cel
numbers by a factor of two!!!! Grrrrrrr.

Please help me with this basic problem

--
Sivods
-----------------------------------------------------------------------
Sivodsi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1478
View this thread: http://www.excelforum.com/showthread.php?threadid=26405

0
9/28/2004 12:59:17 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
283 Views

Similar Articles

[PageSpeed] 48

Look up "Relative" and "Absolute" cell references in Help.

"Absolute" references, depicted by the dollar sign ($), do *NOT* change as
the formula is copied to other cells.

For example:

=VLOOKUP(A1,SHEET2!$A$1:$Z$100,5,0)

Will retain the data list references A1:Z100 throughout any copying of the
formula, even though the lookup cell, A1 *will* change as the formula is
copied down the column.
-- 

HTH,

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


"Sivodsi" <Sivodsi.1d9qhy@excelforum-nospam.com> wrote in message
news:Sivodsi.1d9qhy@excelforum-nospam.com...

Hi everybody,
I apologize for this really obvious problem, I haven't encountered it
before so its really ummm "bugging" me.

I am using the vlookup function, and in the formulae it refers to a
range of figures A1-Z100 in another worksheet. If you copy and paste
the formulae to find the next piece of information xcel automatically
advances the the references, so that now the range of figures it refers
to is A2-Z101, which means that the vlookup function is no longer
looking in the full range of figures. How do I stop it from doing
this?

In the past I just did one row, manually changing the table it refers
to, then copying and pasting that entire row of formulae to the next,
and xcel would get the idea that I didn't want the cell numbers to
advance. But I was using a different version of xcel which is
remarkably insistent in its cell advancing afforts. To avoid this I
manually did two rows of formulae, setting the table array manually and
then copying and pasting both rows. Instead of sticking to the table
array as it should have done, the bloody thing advanced the cell
numbers by a factor of two!!!! Grrrrrrr.

Please help me with this basic problem!


-- 
Sivodsi
------------------------------------------------------------------------
Sivodsi's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=14785
View this thread: http://www.excelforum.com/showthread.php?threadid=264056

0
ragdyer1 (4060)
9/28/2004 1:18:17 AM
Try $a$1:$z$100 (and then drag down)

or you could define a range name that points at A1:Z100.

Select your range and type:
MyRng
(or whatever you want to call it)
and hit enter

Then your =vlookup() becomes:

=vlookup(a1,myrng,2,false)



Sivodsi wrote:
> 
> Hi everybody,
> I apologize for this really obvious problem, I haven't encountered it
> before so its really ummm "bugging" me.
> 
> I am using the vlookup function, and in the formulae it refers to a
> range of figures A1-Z100 in another worksheet. If you copy and paste
> the formulae to find the next piece of information xcel automatically
> advances the the references, so that now the range of figures it refers
> to is A2-Z101, which means that the vlookup function is no longer
> looking in the full range of figures. How do I stop it from doing
> this?
> 
> In the past I just did one row, manually changing the table it refers
> to, then copying and pasting that entire row of formulae to the next,
> and xcel would get the idea that I didn't want the cell numbers to
> advance. But I was using a different version of xcel which is
> remarkably insistent in its cell advancing afforts. To avoid this I
> manually did two rows of formulae, setting the table array manually and
> then copying and pasting both rows. Instead of sticking to the table
> array as it should have done, the bloody thing advanced the cell
> numbers by a factor of two!!!! Grrrrrrr.
> 
> Please help me with this basic problem!
> 
> --
> Sivodsi
> ------------------------------------------------------------------------
> Sivodsi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14785
> View this thread: http://www.excelforum.com/showthread.php?threadid=264056

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
9/28/2004 1:38:31 AM
Reply:

Similar Artilces:

ADVANCE FILTER SORT
Hello! I have this form called "frm_FilterMain" with a button that execute this code "DoCmd.RunCommand acCmdAdvancedFilterSort" to open the advance filter sort fom named by access as "frm_FilterMainMain1 : Filter". After I completed selecting all the fields I wanted, key in all the cretirea, sort, etc then click the apply filter button this advance filter sort fom "frm_FilterMainMain1 : Filter" is still there and doesn't close. Is there any way to close this form as soon as my "frm_FilterMain" is displayed and active. Could anyone...

Copying Cells on a diagonal?
I need to copy cells on the diagonal for a large data set as follows P1 P2 P3 P4 P5 Group 1 P1 5 4 5 4 3 Group 1 P2 5 4 5 4 3 Group 1 P3 5 4 5 4 3 Group1 P4 5 4 5 4 3 Group1 P5 5 4 5 4 3 Group 2 P1 5 4 5 4 3 Group 2 P2 5 4 5 4 3 Group 2 P3 5 4 5 4 3 Group 2 P4 5 4 5 4 3 Group 2 P5 5 4 5 4 3 I need P1's Self score, P2's Self Score, P3's self core etc for each team lined up in a single column. which for the above data would be Self Rating 5 4 5 4 3 5 4 5 4 3 Is a formula like =A2 =B3 =C4 =D5 =E6 the best way to do this and then cut and paste the formula for each group? Than...

Linking a range from an external workbook to a single cell
I am linking a series of workbooks to a summary work book. I need to know if I can enter a cell range from the external workbooks to a single cell in a summary workbook. In this instance I have 12 months data stored in workbook A in 12 seperate columns. I know I can link each cell in workbook A to a corresponding cell in workbook B. However this requires me to link each of the 12 cells in workbook A to the 12 in workbook B. I would like to enter in the first cell in workbook B the start of the range of 12 cells in workbook A and automatically generate the same range in workbook B. I have tri...

auto numbering spread throughout a document
I have word 2007. I'm working on a process memo with numbered steps. The steps are auto numbered. Within this document also have a series of internal controls that are numbered: "internal control 1.", "internal control 2." etc. Is there a way for me to auto number "internal control" so that each time I have to add one to my document it's automatically number and if I remove one it renumbers? These internal controls appear throughout the document and do have a specific formatting. Does this make sense? Thanks Create a Style for ...

Problem with auto correction in cells
When I type ex. 0771 in a cell, excel auto change it to 771. How do I avoid this -- Message posted from http://www.ExcelForum.com Hi if you need a leading zero you have some options: 1. If you don't need to calculate with these values either preformat the cell as 'Text' or enter the value with a leading apostrophe 2. If you need these values as numbers you may apply a custom format to these cells. In your case for example the custom format 0000 >-----Original Message----- >When I type ex. 0771 in a cell, excel auto change it to 771. >How do I avoid this? > &g...

How to count cells excluding repeat info
How do you create a formula or count information in a range of cells an exclude any repeat information? For example, I have vulnerabilitie listed in Column A and IP addresses listed in Column B. I want to b able to count how many machines are in the spreadsheet or how man vulernabilities. Column A Column B MS04-007 69.90.32.44 MS04-007 69.90.32.45 MS04-007 69.90.32.46 MS04-014 69.90.32.44 MS04-014 69.90.32.48 MS03-028 69.90.32.55 MS04-025 69.90.32.44 MS04-025 69.90.32.52 4 Vulnerabilities; 6 Machines I would appreciate any help in this ...

Split cell
Is it possible to split several cells in a column, but not the whole column? Cathy "Split" means what to you? Please explain. Gord Dibben Excel MVP On Wed, 12 May 2004 12:43:01 -0500, "Cathy C" <cathy-remove-christian@sbcglobal.net> wrote: >Is it possible to split several cells in a column, but not the whole column? > Hi, robably not, but what, exactly, do you mean by "split several cells" ?? an example, perhaps? jeff >-----Original Message----- >Is it possible to split several cells in a column, but not the whole column? > > &...

Pasting an object into a cell with a drop down menu
I have made simple Macros to paste objects (Autoshapes) into various cells. Sub Macro1() ' Keyboard Shortcut: Ctrl+q ActiveSheet.Shapes("Group 100").Select Selection.Copy ActiveCell.Select ActiveSheet.Paste ActiveCell.Select End Sub I would like not to use Macros but instead use a Drop Down Menu. Ideas? Ken Objects cannot be pasted "into" cells.........only lay on top. To have a picture show up based on VLOOKUP when you select an item from a DV list or type in a name see JE MCGimpsey's site. http://www.mcgimpsey.com/e...

Alternating Row Formats When Cell Value Changes
I have a list of schools with the school names in column B and the school's district number in column C. I'd like to alternate the row shading each time the district number changes. Is there a formula that can be used for conditional formatting, VB code or some other method to do this? Thanks for any help or advice, Andrew My Excel add-in "Shade Data Rows" does that. It shades groups of like valued rows or will shade by every nth row. Choice of shade colors and an option to skip hidden rows. Comes with a one page Word.doc install/use file. It is free upon direct req...

Protecting Selected Cells and Functions
I have a worksheet. In Cell B2 is a Data validation box Listing a range of colleagues names( DRop Down Menu). On selection of a name in B2, the contents of the whole worksheet changes. I like to Protect the worksheet for: 1) Hiding the formulaes 2) And most importantly preventing editing of the contents of any other cell (except B2). and yet be permiitted to: 3) Select contents in Cell B2 (Data Validation Box) 4) Select Auto filters in Row 4 I've tried using the the Tools/ Protect worksheet menu, ticking Select Lock Cells, Select Unlock cell, use auto filters. And in in Format/Cells/...

Reducing the number of lines of code required
I'm trying to create a form for work and I have the following lines o code created within it... I have another 20 checkboxes which wil require similar lines of code. Is there any way to cut down on th total number of lines required (as it will end up being a ridiculou size otherwise)? CheckBox5.Value = False TextBox14.Enabled = False TextBox14.BackColor = RGB(128, 128, 128) CheckBox6.Value = False TextBox15.Enabled = False TextBox15.BackColor = RGB(128, 128, 128) CheckBox7.Value = False TextBox16.Enabled = False TextBox16.BackColor = RGB(128, 128, 128) CheckBox8.Value = False TextBox17.E...

missing "advanced" button in export
Using A2003 with A2k format. I'm trying to create an export spec. I've done this before but it's been quite a while (and probably under straight A2k). I remember going to the export option and in the screen where you specify the file name and type, you click the "Format" checkbox and then at some point you get a wizard with an "advanced" option. I'm not even getting to the wizard. It seems to be missing. I checked my installation of Access and everything seems to be installed. Can anyone tell me what might be wrong here? THanks, Keith Never mind. I se...

The terror of advancing cell numbers!
Hi everybody, I apologize for this really obvious problem, I haven't encountered i before so its really ummm "bugging" me. I am using the vlookup function, and in the formulae it refers to range of figures A1-Z100 in another worksheet. If you copy and past the formulae to find the next piece of information xcel automaticall advances the the references, so that now the range of figures it refer to is A2-Z101, which means that the vlookup function is no longe looking in the full range of figures. How do I stop it from doin this? In the past I just did one row, manually changing...

associate alphabet letters with numbers?
I want to associate letters of the alphabet with numbers so that when a letter is put in a cell, a numerical value can be associated. Sort of like a phone pad-I want it so that you could put the letter in a cell and a designated number would automatiocaly show up in another cell. Is there a way to create a formula that can do this? Help would be greatly appreciated. Thank you. -- Loriandme69 ------------------------------------------------------------------------ Loriandme69's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28971 View this thread: http://www.exc...

worksheet cell references
The below is an example of a formula that I was shown to get values from other worksheets into my current worksheet, using a cell to reference the appropriate worksheet needed. Is there a way to write this so that the additional string cell reference is not absolute. I would like to copy and paste, but the only reference that changes is the A1. I know that if I put a $ in front of the 1, the A$1 will always reference the same cell, when copied and pasted, for the worksheet name I need to use, but I need to get different information from sequential cells in the worksheets. =INDIRECT...

Conditional formatting depending on sub string content i cell
Hi! This has been eluded me all afternoon, I just want to mark out cells in Excel that contains a specific sub string. I have thousands of rows all containing small text strings. I would like to mark the once containing one specific word with blue text, and other cells containing another specific key word with a different color. Very simple, don't care about corner cases (containing several key words etc.) Mostly I'm annoyed that I cannot solve this simple problem. I just cannot get Excel to understand any of the formulas I try in conditional formatting. Can anyone h...

Advance filter not working #2
I have data lik Sr. no., Date, name, bill no., item, rate, bill amoun When I use advance filter with above data it works some times only I have created criteria range in A1: B7 and data are A3:B135 when I put criteria in name it works some time and after some days data done not return any information about name and other data Any help Shital Sha ...

Excel Chart How do I create Y axis to represent numbers I am using
I am attempting to create a chart that is populating its data from another worksheet in the same book. I am pulling in numbers that range from 75000 to 3000000 and I would like my y axis to be 50K to 350K in multiples of 25K but I can not figure out how to adjust the axis key. Thanks for your help in advance. Krefty Select the axis and press Ctrl+1 (numeral one). Excel 2003 and earlier: In the dialog that appears, click on the Scale tab, and enter the values you want for min, max, and major unit, and don't check the corresponding Auto boxes. Excel 2007: Check Fixed for each of mi...

How do I quickly enter the same number into different cells?
I am entering a "1" into various cells (collating answers from questionnaires). Is it possible to fill the cell with just a mouse click rather than having to type in number 1 each time? Many thanks for your help. Mel Here's a neat trick: Hi-light all the cells you want to enter. type 1 and then CNTRL-SHIFT-ENTER You will fill all the cells at once -- Gary's Student "Cheers100" wrote: > I am entering a "1" into various cells (collating answers from > questionnaires). Is it possible to fill the cell with just a mouse click > rather than...

How do I filter or sort by cell shading?
I have been sent a 2000 row report with just the pertinent rows highlighted by solid color shading (about 300 rows scattered throughout). Is there a way to filter down to just those rows that are shaded, or to at least sort to those rows? Have a look here http://www.cpearson.com/excel/SortByColor.htm -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "urnojfk" <urnojfk@discussions.microsoft.com> wrote in message news:...

read a column of names and place a number in the next cell
I want to automatically read a column of suburb names and add a column of matching street directory map numbers. I have to do this monthly on several different worksheets. Judy If you have a table with the suburbs and matching map numbers you could use the VLOOKUP Function. Assume on sheet2 you have column A with suburbs, column B with matching numbers. On sheet1 you have suburbs names in Colimn A. In B1 enter a VLOOKUP formula =VLOOKUP(A1,SHEET2!$A$1:$B$200,2,FALSE) Double-click on the fill handle to copy down. Gord Dibben Excel MVP On Wed, 7 Dec 2005 14:43:03 -0800, "Judy ...

insert an order number when another sheet opens
I have a form and every time I open it I need it to create a new number. Is this possible. also if I put it a number that does not correspond to an other number can it give me a warning. Thank you so much for your help Carmen cbucco@buccocouture.com ...

Worksheet name in cell
Hi, Is it possible to set the worksheet name for a cell. Eg. In cell A1, how to set A1="Sheet1" if Sheet1 is the worksheet name. If the worksheet name changes to "abc" then A1 should reflect "abc". Any help in this regard is appreciated. Thank You. Regards, Giri Giri, Try =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,32) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Giri" <giri_here@yahoo.com> wrote in message news:0...

Checking for black cells
Hi, I have the following formula on my worksheet: =ROUNDUP((C3/D3) + (F3/G3) + (I3/J3) + (L3/M3) + (O3/P3) + (R3/S3) + (U3/V3) + (X3/Y3) + (AA3/AB3) + (AD3/AE3) + (AG3/AH3) + (AJ3/AK3) + (AM3/AN3) + (AP3/AQ3) + (AS3/AT3) + (AV3/AW3) + (AY3/AZ3) + (BB3/BC3), 0) The formula doesn't work if any of the cells are empty, how can I alter it so that it checks for empty cells and only calculates populated cells? Thanks for your advice Well, one way would be: =ROUNDUP(SUM((IF(D3=0,0,C3/D3)+IF(G3=0,0,F3/G3)+IF(J3=0,0,I3/J3)+IF(M3=0,0,L3/M3)+IF(P3=0,0,O3/P3)+IF(S3=0,0,R3/S3)+IF(V3=0,0,U3/V3)),(...

Cell Reference locking
I am trying to lock a cell reference from one sheet to another. i.e. Cell A1 on "sheet 1" needs to ALWAYS be referenced by cell D5 on "sheet 2." I have tried using $ signs and using the sheet protection to no avail. What happens is when ever a user moves the information via drag and drop or copy and paste (other than copying and pasting up in the tool bar) the cell reference moves with it. i.e. User highlights the cell B1 on "sheet 1" and drags it to cell A1. On my machine what happens is that Cell D5 is now referencing B1 and the cell that was refe...