Duplicating cells with formula's

I'm havnig a problem that I just can't figure out...
I have the formula....

=IF(ISNUMBER(VLOOKUP(A2,Sheet3!A3:B280,2,FALSE)),VLOOKUP(A2,Sheet3!
A3:B280,2,FALSE),"")

I need to copy the formula down a column from row 3 - 280 only
changing the A2 after each vlookup to the next corrisponding A3, a4,
a5.  etc. When I drag the little plus down it copies it but adjusts it
incorrectly.

Example of how I want it to look....
This formula would go in Column A Starting Row 2
Row 2:=IF(ISNUMBER(VLOOKUP(A2,Sheet3!A3:B280,2,FALSE)),VLOOKUP
(A2,Sheet3!A3:B280,2,FALSE),"")
Row 3:=IF(ISNUMBER(VLOOKUP(A3,Sheet3!A3:B280,2,FALSE)),VLOOKUP
(A3,Sheet3!A3:B280,2,FALSE),"")
Row 4:=IF(ISNUMBER(VLOOKUP(A4,Sheet3!A3:B280,2,FALSE)),VLOOKUP
(A4,Sheet3!A3:B280,2,FALSE),"")
etc to Row 260

Example of how it actually looks.....
Row 2:=IF(ISNUMBER(VLOOKUP(A2,Sheet3!A3:B280,2,FALSE)),VLOOKUP
(A2,Sheet3!A3:B280,2,FALSE),"")
Row 3:=IF(ISNUMBER(VLOOKUP(A3,Sheet3!A4:B281,2,FALSE)),VLOOKUP
(A3,Sheet3!A4:B281,2,FALSE),"")
Row 4:=IF(ISNUMBER(VLOOKUP(A4,Sheet3!A5:B282,2,FALSE)),VLOOKUP
(A4,Sheet3!A5:B282,2,FALSE),"")

I'm tried using the replace tool, but Can't get it right.
Without manually typing it, lots of formulas to retype.

Any help would be greatly appreciated..
Excel 2007


Thanks!
Shaun
0
shaunholtz (11)
8/13/2009 8:47:47 PM
excel 39879 articles. 2 followers. Follow

3 Replies
642 Views

Similar Articles

[PageSpeed] 45

You need to fix the references to the table so that it does not change
when you copy it down. You do this by putting $ symbols in front of
(in this case) the row references, and in doing so they become
absolute references rather than relative - you can find out more in XL
Help, but your formula would become:

=3DIF(ISNUMBER(VLOOKUP(A2,Sheet3!A$3:B$280,2,FALSE)),VLOOKUP(A2,Sheet3!A
$3:B$280,2,FALSE),"")

Now when you copy it down the table will not change.

Hope this helps.

Pete

On Aug 13, 9:47=A0pm, Storm_21_924 <shaunho...@comcast.net> wrote:
> I'm havnig a problem that I just can't figure out...
> I have the formula....
>
> =3DIF(ISNUMBER(VLOOKUP(A2,Sheet3!A3:B280,2,FALSE)),VLOOKUP(A2,Sheet3!
> A3:B280,2,FALSE),"")
>
> I need to copy the formula down a column from row 3 - 280 only
> changing the A2 after each vlookup to the next corrisponding A3, a4,
> a5. =A0etc. When I drag the little plus down it copies it but adjusts it
> incorrectly.
>
> Example of how I want it to look....
> This formula would go in Column A Starting Row 2
> Row 2:=3DIF(ISNUMBER(VLOOKUP(A2,Sheet3!A3:B280,2,FALSE)),VLOOKUP
> (A2,Sheet3!A3:B280,2,FALSE),"")
> Row 3:=3DIF(ISNUMBER(VLOOKUP(A3,Sheet3!A3:B280,2,FALSE)),VLOOKUP
> (A3,Sheet3!A3:B280,2,FALSE),"")
> Row 4:=3DIF(ISNUMBER(VLOOKUP(A4,Sheet3!A3:B280,2,FALSE)),VLOOKUP
> (A4,Sheet3!A3:B280,2,FALSE),"")
> etc to Row 260
>
> Example of how it actually looks.....
> Row 2:=3DIF(ISNUMBER(VLOOKUP(A2,Sheet3!A3:B280,2,FALSE)),VLOOKUP
> (A2,Sheet3!A3:B280,2,FALSE),"")
> Row 3:=3DIF(ISNUMBER(VLOOKUP(A3,Sheet3!A4:B281,2,FALSE)),VLOOKUP
> (A3,Sheet3!A4:B281,2,FALSE),"")
> Row 4:=3DIF(ISNUMBER(VLOOKUP(A4,Sheet3!A5:B282,2,FALSE)),VLOOKUP
> (A4,Sheet3!A5:B282,2,FALSE),"")
>
> I'm tried using the replace tool, but Can't get it right.
> Without manually typing it, lots of formulas to retype.
>
> Any help would be greatly appreciated..
> Excel 2007
>
> Thanks!
> Shaun

0
pashurst (2576)
8/13/2009 9:02:09 PM
Perfect!!!  That's exactly what I'm looking for.. I've been racking my
brain, completely forgot about absolutes vs relatives.

Thanks for you help!

0
shaunholtz (11)
8/13/2009 9:08:01 PM
You're welcome, Storm - thanks for feeding back.

Pete

On Aug 13, 10:08=A0pm, Storm_21_924 <shaunho...@comcast.net> wrote:
> Perfect!!! =A0That's exactly what I'm looking for.. I've been racking my
> brain, completely forgot about absolutes vs relatives.
>
> Thanks for you help!

0
pashurst (2576)
8/13/2009 9:31:08 PM
Reply:

Similar Artilces:

Duplicates
One worksheet has 600 + lines with a lot of expenses, including SOME of the local expenses. This is a check register.. Another worksheet has 300 + lines with ONLY local expenses. Trying to get ALL expenses together, but eliminating the duplicates from new list. Thinking B2=date. C2=name, E2=amount - - SO B2&C2&E2 on first long worksheet and then the same on short worksheet... Bring BOTH over to new tab and then stuck. Any ideas greatly appreciated, or another better way to attack it. You could use excel filter: Data - Filter - Advanced Filter - Unique records on...

Which Office X 10.1.5 duplicate fonts can be removed from which folders?
Which of these duplicate fonts can be removed from the Applications/Microsoft/Office X/Office/Fonts folder AND/OR the User/nnager/Library/Fonts folder? All of the following are duplicated in the two folders. Two of them, Times New Roman and Verdana, also are in OS 10.3.3/Library/Fonts. Arial Arial Black Century Gothic Comic Sans MS Copperplate Gothic Bold Copperplate Gothic Light Curlz MT Edwardian Script ITC Impact Lucida Handwriting Monotype Sorts Tahoma Times New Roman Verdana Wingdings Respectfully, Norm On Tue, 6 Apr 2004 04:42:59 -0400, Norman R. Nager, Ph.D. wrote (in article <...

Paste Special drops cells from Word and Excell
Our church is using Publisher 2003 to do our bulletin and newsletter. When we try and use Paste Special to paste the calendar from Word 2003, it drops the last row of cells bottom of calendar (they do not show up in Publisher). If we go back to Word 2003, new document, and paste, everything is there. The same thing happens when we try to Paste Special from Excel 2003. It drops the last column of the Excel sheet. Any suggestions on how to fix this? The calendar and Excel sheet are put together by another individual ( not the secretary) and emailed to the church office. The secre...

Help with a complex formula
Hi, I'm having trouble figuring out how to set a second condition within a formula where one condition is met but I need another condition in the formula such that even if the first condition is true, if the second condition is not true I want the result to be a blank cell. Here is whats going on. In cell B8 is this formula =IF(ISBLANK($A8)," ",VLOOKUP($A8,MON!$A$11:$C$29,2,FALSE)) I enter a name in A8 and if it finds that name in MON!$A$11:$C$29 it returns the value in column 2 of the range. (This part works fine) When i go to K8 and enter this formula =IF(ISBLANK($A8),&q...

think cell program
is anyone familiar with a program called think cell? Any thoughts? (powerpoint v 2003). Is this an add-in? thanks Sara It's very good. You can "try for free" from their website: http://www.think-cell.com/ Recent interview with one of the founders on Indezine: http://blog.indezine.com/2009/12/think-cell-conversation-with-markus.html -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover Kit http://tinyurl.com/3...

Sum range of cells with VBA
Hi all, I need to add a range of cells with VBA whose length which varies each time. Within my code I have written the following. Although I can locate the last cell address within the range the sum function does not return the value. Can someone please correct my code or tell me what i am doing wrong. ' Total Debit and Credit columns Dim TtlDr As Double Dim TtlCr As Double Dim LastRowDr As Range Dim LastRowCr As Range Set LastRowDr = ActiveWorkbook.Worksheets ("Datasheet")...

Duplicate data from field into another field in same form?
Can I duplicate data from one field on a form into another field in the same form? For example: On our exhibitor entry form, the Program Contact info (fields for name, address, phone, fax, email) is entered first. Mail Contact info is often the same, but not always. Is it possible to autopopulate the Mail Contact with the data from the Program Contact, but allow me to enter new data if necessary? If so, can you tell me exactly how? Thanks! -- Andi On Fri, 23 Mar 2007 09:46:24 -0700, Andi <Andi@discussions.microsoft.com> wrote: >Can I duplicate data from one field on a ...

Cant merge cell
When I merge two cells , the contents of which are X and Y, I get only one X in the merged cell. Worthless... I would like to get XY This is not possible in Excel, but if you wish you can use this formula in another cell, If X is in cell A1 and Y is in cell B1, you can write this formula in any cell: =CONCATENATE(A1,B1) I am no expert, maybe somebody else can come up with a better solution Regards "Dadada" wrote: > When I merge two cells , the contents of which are X and Y, I get only one X > in the merged cell. Worthless... > I would like to ge...

MS Excel VBA Pivot table link cell fetch records
I have a table 'tbl_Final' in MS Access 2007 where the data is used and cached in a pivot table found in the 'Data' worksheet in MS Excel 2007. In this MS Excel 2007 workbook, I have various worksheets reports which links to the pivot table values found 'Data' worksheet. I linked formulae in one of the cells found the worksheet reports, looks like this: =GETPIVOTDATA("Amount",Data!$B$4,"Month", 2,"Year","2008","Scenario","Budget") Lets say the cell value total is: 1000 So, In pivot: 1000 ...

Receiving duplicate emails
I have just recently created my email account in Microsoft Outlook and now when I receive email I get the same one 15 times (and that is no stretch!) Is there something I have done wrong? How do I fix this?! Thanks What version of Outlook do you have? What sort of mail account(s)? Do you only get a single message many times, or all the messages many times? -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "Carrie" <cadelaney@rogers.com> wrote in message news:0a0e01c3afaf$f8efab60$a001280a@phx.g...

Duplicate, Duplicate, dupli ...
Hi, I'm new to the group, so hope I don't disgrace myself. I have a list of names in the first column, and their respective postal codes in the second. Unfortunately, many of the names, (and respective codes), are duplicated... sometimes there are up to four of the same name, (and code), under each other. Is there a way to get rid up the duplicates and make a usable list please? I can do it manually of course, but .. .. Thanks. Dave Dave Data>Filter>Advanced Filter. "unique records only" and "copy to another location" For more on this visit Debra Dalgl...

Quit creating duplicate desktop icons when doing upgrades/hotfix
Upon any sort of upgrade or hotfix, RMS creates a whole new set of desktop icons. What a pain! Pls fix. Ask if we want to create new desktop icons, pls. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/...

How do I duplicate numbering on a raffle?
I am creating an event ticket but would like to number the tickets on both the left and right sides of the ticket. Create a data base with your numbers. Use mail merge for the tickets. You can insert a mail merge field on both sides of your ticket. When you merge have one ticket on your screen. What version Publisher are you using. Some early versions of Publisher shows all the merge items the same in print preview. This is a Publisher bug. Mail, e-mail, and catalog merge http://office.microsoft.com/en-us/publisher/CH100502901033.aspx -- Mary Sauer http://msauer.mvps.org/ "Candi...

How to delete "non-identical" duplicate records in an Access table
How to delete "non-identical" duplicate records in an Access Table? Where "non-identical" duplicate record means a record in the table that has slightly different datum in one of the fields, but an identical duplicate datum in the field that I am concerned with. For example: SSN MRN CLIENT NAME 001-00-2222 11170419 Smith, Jane 001-00-2222 11170419 Smith, Jane T 001-00-2222 11170419 Smith, Jane Thompson The data of these two records in the fields SSN and MRN are identical; but "non-identical" in the CLIENT NAME field (notice...

How do I color one cell and have another cell get same color
I want to assign a yellow color to either of 2 cells and have a seperate cell turn the same color As far as I know, Excel does not recognize a color change as an event to which it will react. The same for Conditional Formatting. Perhaps you can work with the condition that drives you to color either of those 2 cells. Post back and provide more info on what you have and what you want to have. HTH Otto "glassman" <glassman@discussions.microsoft.com> wrote in message news:A5B1B2B1-A641-4EC3-809A-5F91E16EE46E@microsoft.com... > I want to assign a yellow colo...

How do I get brackets in format cells accounting?
In Excel 2000, "format cells accounting" got me brackets around negative numbers. Excel 2003 gives me a negative sign. Is there any to change this? I know I can get the format I want in custom, but this seems to require formatting each cell. Highlight all the desired cells. Right click and select Format Cells. From the Number tab you can select the desired view. "allan" wrote: > In Excel 2000, "format cells accounting" got me brackets around negative > numbers. Excel 2003 gives me a negative sign. Is there any to change this? > I know I c...

How do i turn the spreadsheet into a formula page?
I had to make a spreadsheet for a sales budget for my accounting course and my professor wanted us to show him the spreadsheet in a formula form ...such as ... =d4*c5 something like that....now the problem is that i dont kno what button to push to make the spreadsheet turn into formulas like that one i jus showed....can someone please help me??/.....is that enough info gord dibben?? sameeha wrote: || I had to make a spreadsheet for a sales budget for my accounting || course and my professor wanted us to show him the spreadsheet in a || formula form ...such as ... =d4*c5 something like tha...

Duplicate ItemLookupcode
Any clues on how I could get duplicate itemlookupcodes?? I found a few of them recently. No SQL queries have been run to generate them and if I try to create a duplicate itemLookupCodes I am stopped with a message telling me to use a unique name. How could they have gotten in the database? Any clues, boos or comments appreciated. Thanks for all the help! Jamie W This is a multi-part message in MIME format. ------=_NextPart_000_001F_01C755DB.F64982C0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: quoted-printable As you mentioned, RMS Manager doesn't ...

Split cells
Is it possible to split a cell diagonally and put seperate information in each half? thanks -- "Rent a villa in Turkey" http://www.rent-in-turkey.com/html/home.htm I guess you could type something, use alt + enter a few times, type something again, pad with some spaces and use format>cells>border to insert a diagonal border. However I would strongly suggest you use multiple cells like A1 and B2, then maybe something from the drawing tools to insert a diagonal line from A2 to B1 -- Regards, Peo Sjoblom Portland, Oregon "Scudo" <me...

Duplicates!
Why do the songs in my media player library often duplicate or tripulicate themselves, so i end up with a library three times the size it should be. I have on occassions gone through and deleted all the replicated songs, but over a period of time they seem to "grow again" filling my library again with unwanted duplicates, taking up more space and memory. Is there anyway i can stop this happening????? Thanks. What version of WMP/Windows? First, make sure that WMP isn't monitoring the same folder twice. Duplicate songs can appear when, for example, both C:\Music a...

Find duplicates in a column
Hi, I have 6000 emails addresses in a col and wish to know if there are any duplicates. How can I do this please. I have Excel 2002. rock Assuming your emails are in A1 to A6000, in B1 enter =COUNTIF($A$1:$A$6000,A1) copy down B1 all the way to B6000 Apply a conditional format of red pattern on cells B1 to B6000 where Cell is greater than 1 and all duplicates will be shown in red. "rock" wrote: > Hi, > > I have 6000 emails addresses in a col and wish to know if there are any > duplicates. > > How can I do this please. > > I have Excel 2002. > ...

Maximum Number of Cell Formats
My company uses very large spreadsheets to confrom to goverment requirements. Recently we have run into a Warning message when we try to insert a new column. It says we've used up all the cell formats, so we can't add any new cells. The Microsoft documentation seems to indicate that the maximum number of cell formats is 4000. I guess we're hitting that limit and Excel won't let us enter anymore data. How do I increase the max number of cell formats? How do I find out how many I've used already? How do I delete ones I'm not using (Excel seems to "hang on&quo...

Format a cell with numbers and user defined text
I want to format a cell that will take user defined numbers and text in the following format: 12345678-A Another example would be: 23454368-X The numbers, dash, and text refer to Medicaid identification numbers and I want to be able to have a constant format when these values are entered. ...

duplicate emails received
I've been receiving duplicate emails. This doesn't happen with all the emails I receive, only with some of them. "Leave a copy on the server" is not checked. I don't know the reason why this happens. Can somebody help me. Thanks in advance. ...

slanted cell shading
I am wishing to create the shading as shown on the 2nd image/chart on this page: http://office.microsoft.com/en-us/assistance/HA011097991033.ASPX I'm trying to get the diagonal shading. Everytime I try to shade an area, it shades the whole cell, and I'm only wishing to shade half. I have already changed the Orientation and have the text set at a 68º angle. Whenever I try to shade the area (left click cell, right click, format cells, Patterns, and choose a cell shading color) it shades the whole block and not just the diagonal area. Please explain in detail how I am t...