formula question #3

I am using the following fomula
=LOOKUP(c3,Lists!$aa2:$aa113,Lists!$ab2:ab113)
the formulas works great, however in trying to copy the formula, it adds a 
number to the list values and looks like this.
=LOOKUP(c3,Lists!$aa3:$aa114,Lists!$ab3:ab114)
the list I am wanting to work with starts at aa1 and ends at aa113, so this 
will not work. 
I do not want to edit the formula in each cell, as I am wanting to fill 
acouple thousand cells. 
any suggestions?
0
abraun (1)
3/18/2009 4:07:03 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
522 Views

Similar Articles

[PageSpeed] 39

Hi,
You need to add more dollars.
Change =LOOKUP(c3,Lists!$aa2:$aa113,Lists!$ab2:ab113)
to =LOOKUP(c3,Lists!$aa$2:$aa$113,Lists!$ab$2:ab$113)

$a locks the column $2 locks the row.
John


"abraun" <abraun@discussions.microsoft.com> wrote in message news:F566456A-4E0F-46CD-BFEF-7CAB63EEA708@microsoft.com...
>I am using the following fomula
> =LOOKUP(c3,Lists!$aa2:$aa113,Lists!$ab2:ab113)
> the formulas works great, however in trying to copy the formula, it adds a 
> number to the list values and looks like this.
> =LOOKUP(c3,Lists!$aa3:$aa114,Lists!$ab3:ab114)
> the list I am wanting to work with starts at aa1 and ends at aa113, so this 
> will not work. 
> I do not want to edit the formula in each cell, as I am wanting to fill 
> acouple thousand cells. 
> any suggestions?
0
me5306 (285)
3/18/2009 4:16:52 PM
"abraun" <abraun@discussions.microsoft.com> wrote in message 
news:F566456A-4E0F-46CD-BFEF-7CAB63EEA708@microsoft.com...
>I am using the following fomula
> =LOOKUP(c3,Lists!$aa2:$aa113,Lists!$ab2:ab113)
> the formulas works great, however in trying to copy the formula, it adds a
> number to the list values and looks like this.
> =LOOKUP(c3,Lists!$aa3:$aa114,Lists!$ab3:ab114)
> the list I am wanting to work with starts at aa1 and ends at aa113, so 
> this
> will not work.
> I do not want to edit the formula in each cell, as I am wanting to fill
> acouple thousand cells.
> any suggestions?


Edit the original formula to be
=LOOKUP(c3,Lists!$aa$2:$aa$113,Lists!$ab$2:ab$113)
See the extra $ signs?

-- 
Asking a question?
Please tell us the version of the application you are asking about,
your OS, Service Pack level
and the FULL contents of any error message(s) 

0
3/18/2009 4:17:20 PM
Hi
Click on the cell with the correct formula
Go to the formula bar
place your cursor in the $aa2:$aa113 and $ab2:ab113
press F4 key till you see
=LOOKUP(c3,Lists!$aa$2:$aa$113,Lists!$ab$2:$ab$113)
Excel will add the $ for you, then copy down

-- 
Hope this help

Pls click the Yes button below if this post provide the answer you asked


cheers, francis
"abraun" <abraun@discussions.microsoft.com> wrote in message 
news:F566456A-4E0F-46CD-BFEF-7CAB63EEA708@microsoft.com...
>I am using the following fomula
> =LOOKUP(c3,Lists!$aa2:$aa113,Lists!$ab2:ab113)
> the formulas works great, however in trying to copy the formula, it adds a
> number to the list values and looks like this.
> =LOOKUP(c3,Lists!$aa3:$aa114,Lists!$ab3:ab114)
> the list I am wanting to work with starts at aa1 and ends at aa113, so 
> this
> will not work.
> I do not want to edit the formula in each cell, as I am wanting to fill
> acouple thousand cells.
> any suggestions? 


0
xlsmate (17)
3/18/2009 4:53:20 PM
You would probably also find it usefull to search the Help for "About cell 
and range references" and read up on the "The difference between relative and 
absolute references"

Mike

"abraun" wrote:

> I am using the following fomula
> =LOOKUP(c3,Lists!$aa2:$aa113,Lists!$ab2:ab113)
> the formulas works great, however in trying to copy the formula, it adds a 
> number to the list values and looks like this.
> =LOOKUP(c3,Lists!$aa3:$aa114,Lists!$ab3:ab114)
> the list I am wanting to work with starts at aa1 and ends at aa113, so this 
> will not work. 
> I do not want to edit the formula in each cell, as I am wanting to fill 
> acouple thousand cells. 
> any suggestions?
0
mikebres (18)
3/21/2009 11:13:01 AM
Reply:

Similar Artilces:

problem with index formula
The following fomula is contained in cell C16 and returns the correct values: =VLOOKUP(B6,'Active Clients'!$B$2:$K$954,10,FALSE) The following formula is contained in cell L18 and uses the result in C16 as a lookup value in the first "match": =INDEX(L21:M44,MATCH(C16,L21:L44,1),MATCH(M16,L21:M21,-1)) My promlem is that when I try to match on cell C16 my index formula returns an error. However, when I change the index formula to reference another cell (let's say cell D16) and I type in the value shown in cell C16, the index formula works fine. I think my problem has ...

Send email to custom entities in CRM 3.0
How do I send email to entities other than account, contact, leads and users? Not possible. "Andy" wrote: > How do I send email to entities other than account, contact, leads and > users? > I found out the following: It turns out every CRM 3.0 email is made out of three types of records, all of which are tied together through a common ActivityId: activitypointer - this is the main record and it holds the fields common to all activities email - this holds fields uniquely related to emails such as the mime type, tracking token, etc. activityparty - this holds sender and...

IF FORMULAS #2
Looking for help...... In column A1:A16, I have names. In B1:B16 I will assign random numbers to those names from 1-16. In another part of the sheet I have 16 columns lets say D:S, (D) being column 1 and (S) being column 16. when I assign a number in column B, I want the name to the left to appear above the appropiate column. EX, If B1 is 16 then the name in A1 will appear above column S. I am using Excel 2007 Windows Vista. I find this site to be the place for answers,everyone is great. Thanks in advance. D1: =INDEX($A$1:$A$16,MATCH(COLUMN()-3,$B$1:$B$16,0)) Fill to S1. "JOHN...

How do I display a formula calculation in plot area of a chart?
I want to add a text box in the chart that returns the value from a formula that was entered on the data page. The formula value is not related to any series value. WiseGuyOne: You need to build your text expression in a cell, then insert that expression to the chart. First, to build your text expression: 1. Select your cell 2. Enter = first to let Excel =know you are adding a formula 3. You can now enter text within quotes and cell values .. here's an example =" Yesterdays average temperature was " & sheet1!$a$3 Note that the & is used to add ...

Personal.Xls #3
Hi There, I changed some code in a macro inside my Personal.xls file and saved it. Now when I try to run the macro I get an error message saying that a file called Personal.xls is already open and I can not open another. What have I done wrong? Thanks for your help, Steve Wilson. It's OK I have sorted this out. I saved Personal.xls to another drive and when I opened Excel it loaded Personal.xls from the XLStart Dir as usual but when I clicked on my macro button in the toolbar it was pointing to the macro in personal.xls on the other drive. All the best, Steve Wilson. "Alway...

Excel question ?
Hi, can anyone help me out with this .... I have excel 2003 table and sheet inside of it have some formulas. I would like to paste text data from notepad to that sheet without of erasing any of the containing formulas which are like I said already there. Thanx Hi Steve You could create a textbox from the ">View >Toolbars > Drawing menu, then paste your information in it. The textbox can be size and position any place on your sheet. HTH John "Steve" <steveaa@dzemail.com> wrote in message news:id0ci1$mh2$1@localhost.localdomain... > Hi, can anyone help me o...

Need Formula to Average Monthly Increase
I have a spreadsheet where there are columns for each month of the year Each cell contains a number of loan submissions that graduall increases per month as we gain more business from each mortgage vendor I need a formula that can look at multiple cells in a row (i.e Jan-Dec) per vendor and then give me a result that shows the averag monthly percentage increase factor in submissions... Example: MONTH Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec A SUBS 1 5 18 29 30 40 57 85 150 275 ......... ..I will be using this data fo forecasting future submissions ...

Going Back in History formula?
How can I express a formula that would back 300 rows and look at every other row for the 3 cells with random single digits from 0 to 9 and calculate, the how many rows back since the oldest 0-9 digit occurred in cell 1 answer, cell 2 answer and 3 answer, then calculate which 0-9 digit has not occurred in cell 1, cell 2 and cell 3 the longest and displays how many row back since the last occurrence. So that would be 4 separate formulas ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Exc...

How do I enter a time formula into a worksheet that determines if.
How do I, set about entering a formula within a worksheet that determines if the Time is greater or less that the time in a certian cell; eg =if(now()>K17,"then display this","") K17 holding the Value 08:30 AM formated to time =IF(MOD(NOW(),1)>K17,"then display this","") HTH Jason Atlanta, GA >-----Original Message----- >How do I, set about entering a formula within a worksheet that determines if >the Time is greater or less that the time in a certian cell; >eg =if(now()>K17,"then display this","") ...

Existing formula
I'm working with a spreadsheet that was built by someone else and cam across a formula in some of the cells that I'm puzzled by. The formul begins with "=", but the next character is "+", then a reference t another worksheet and cell. The "+" is what I'm wondering about, haven't seen it used in this context before, can someone help me ou here? Some examples of what I'm looking at are: =+Inputs!G9 =+H17-R20 Thanks, wwhit -- Message posted from http://www.ExcelForum.com It's an unnecessary character that is there because the person ...

Another Edit Question
I have a form where, at first glance, only 1 field in automatically editable.. What I want is a button that releases all fields to editable mode. If anyone can help I sure appreciate it. Thanks in Advance! What I typically do is to use the controls' TAG property to be able to identify in code those fields I want to lock. I use something like LOCK in that tag and put that in the tag of each control I want to be able to lock and unlock. Then I use this to lock them (usually on the form's On Current event so it starts off locked): Dim ctl As Control For Each ctl In Me.Contro...

Ex 5.5 DL Question
Is it possible to limit it which users can sent mail to a Distribution list within my Exchange Org? an example of this is I dont want just anyone to be allowed to send mail to my "everyone" group.....I only want to allow Management & IS to have this ability. Thanks in advance for any help. Brian That's a feature of later versions of Exchange. "Brian Ullrich" <brianu@nospamtechinfo.cc-nospam> wrote in message news:Ockg8v9MEHA.3668@TK2MSFTNGP11.phx.gbl... > Is it possible to limit it which users can sent mail to a Distribution list > within my Exc...

formulas changing when emailed
At work, I have created a spreadsheet that has links to workbooks in a user drive..called the "U: drive." When I transfer this excel project to others via email, I make sure that the other workbooks also have the same name in their U: drive as well. However, whenever I email it... I have to change many of the formulas at their computers bc the references in my formulas change to a really long string. I have tried using "CNTL+H" to replace the "=" to a "+" but it doesn't work for me bc I have "+" "=" elsewhere in my formulas....

CImageList and CImage question
I want to use an image list with a list control. I am using CImage to load a bitmap file (actually several files) like: CImage image; image.Load("test.bmp"); How do I add this image to a CImageList, which can then be used by the list control? -- Regards, Frank This looks ugly but seems to be the only way. CImage image; image.Load("test.bmp"); CBitmap Bmp; Bmp.Attach(image.Detach(); m_ImageList.Add(&Bmp,RGB(0,0,0)); Bmp.DestroyObject(); image.Load("test2.bmp"); Bmp.Attach(image.Detach(); m_ImageList.Add(&Bmp,RGB(0,0,0)); Bmp.DestroyObject(); .... ...

XSD One Multiple element question
Hi, I am trying to define a Key element for a vehicle schema. I have three ways to uniquely identify a vehicle such as URN, UnitID and Vin. I'd like to make a rule that ensures that at least one of the three is present, but 2 or all 3 can be present. I tried something like below, but it does not seem valid. Thanks in advance for any assistance anyone may be able to provide. Portion of schema is posted below. Hopefully it gives some idea as to what I am trying to accomplish. Best regards, Victor <xs:complexType name="VehKeyType"> <xs:choice> <xs:seq...

Reply Address #3
Using Exchange 2003 and outlook 2003 is there a way to force the "reply to address" to be something besides the Sending address. The example is listed below. These are the sending addresses PHostetter@domainname.com JRoetman@domainname.com DKrischel@domainname.com This is the address I want all replies to be returned to. dp@domainname.com This is the address I want all replies to be returned to. On Sat, 25 Jun 2005 07:30:19 -0500, "Roger Crawford" <rcrawford@nospam.scci.com> wrote: >Using Exchange 2003 and outlook 2003 is there a ...

Excel 2003 - VBA
Hi Guys: I'm drawing a blank and need some help. I have a table with row 3 containing sheet names (currently pointing at 106 sheets). In rows 4-250 I want to put formulas that will look for something (located in Column B) in each of the sheets named in row 3. The first cell in the target sheet contains the last row with valid data. Example: Cell "D3" = P02-05 Cell "B5" = 123456789IBM In cell "D5" I want to generate a formula that will look in sheet P02-05, search for the contents of "B5" throughout the sheet and return the Value in colum...

OWA Compatibilitiey Question..
Can users homed on an Exchange 2003 back-end server access their email through an Exchange 2000 front-end OWA server? Currently when they try it *appears* to work (it doesn't reject their logon credentials), but the page only loads partially with a lot of little red X image placeholder icons. Thanks! jim On Sat, 25 Jun 2005 10:50:52 -0400, "jim" <jim@NOSPAM.com> wrote: >Can users homed on an Exchange 2003 back-end server access their email >through an Exchange 2000 front-end OWA server? Currently when they try it >*appears* to work (it doesn't reje...

Volume Shadow copy question
Hi, I have E drive, where Volume Shadow copy takes snapshot of this drive, and copies them to G: drive If I loose E: drive, and re-install windows 2003 storage server with a new disk, how do I access G: drive so I can copy from Shadow copy. G: drive is used to backup E: drive using volume shadow Thanks MC ...

simple can't find form question
I have a form with some simple code behind it which reads like this: with Forms![frmNextFixtureTypeOption] .[lblNextAlpha].Caption = strNextAlpha .[lblNextNumeric].Caption = strNextNumeric end with stDocName = "frmNextFixtureTypeOption" DoCmd.OpenForm stDocName, , , stLinkCriteria the second part (where I open the form) works fine, but the first (where I set the caption for 2 of the controls) does not - it returns an error "can't find form"... as always, many thanks in advance, mark O...

Excel Cell Formatting #3
How do I centeer a picture in an Excel cell? Hi you can't as such objects 'float' above the cells in Excel -- Regards Frank Kabel Frankfurt, Germany "Tom Coffey" <Tom Coffey@discussions.microsoft.com> schrieb im Newsbeitrag news:8E7356E1-4318-42F3-B9C6-3D7EDFBBEEA1@microsoft.com... > How do I centeer a picture in an Excel cell? ...

Help with referencing multiple sheets in formulas
Greetings Xcellers I have a workbook with 32 sheets, The first sheet is a month summary and the other 31 sheets are for each day of the month. The sheets are names month,1,2,3..31 I need to copy information from the daily sheets to create a summary on the month sheet like follows A B 1 Day Turnover ..... 2 1 ='1'!E7 3 2 ='2'!E7 4 3 ... 5 5 ='5'!E7 Is there a way to substitute the sheet name from the column Day instead of manually having to enter each formula. There will be a number of columns and I have a number of similar workbooks to make. IE for B2 a formula l...

One more Newbie Question
We would like to print the customer's name on the receipt. I have no idea how to do that. It feeds too much blank paper through - after each transaction it feeds about 4" of blank paper. How do you stop it printing the credit card receipt info on the receipt? We use a separate credit card machine that prints its own receipt. It would be nice not to have to enter any credit card info, except maybe the approval code, but RMS forces us to enter the credit card info before being able to processing the transaction - a real drag. Thanks for the help! -- Bud Izen ...

Dll
I have some few basic questions- 1. Is there any api that indicates the relation between some code that is executed and the thread context in which its really belongs? 2. I have an MFC application and an MFC regular dll that exports it functions. The main thread of the application calls some exported function in the dll. The "theApp" object is used inside it, by means of using its class functions. How do I use the dll class functions from another thread? Should I instantiate another object like "theApp2"? 3. Can I use global critical section objects to synchronize between...

formula numeric input
Is there any way to have Excel assume, as it should, that a number input in a formula means a value? For example, is there a way to change the default so that instead of inputting "+7+8" to calculate 15, that I could just input "7+8". Lotus used to work this way, and I have to believe that Excel would allow the same capability. -- seamaml ------------------------------------------------------------------------ seamaml's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30296 View this thread: http://www.excelforum.com/showthread.php?threadid=...