Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Extend total # of rows in excel?

    Is it possible to extend the number of rows a spreadsheet has? I have a table that needs 115000 rows. I copy and pasted 65505 rows and that seemed to be the end. Does anyone have a clue how to do that?

    Thanks.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Not possible. About the only option is to paste into two or more spreadsheets. But the 65536 limit will not change.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    is it possible to write a vlookup to search two different worksheets?

  4. #4
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    is it possible to write a vlookup to search two different worksheets?
    Yes.

    You can use nested IF statements to test for certain criteria or perform equations on them. i.e. =IF(A1=X,VLOOKUP1(),VLOOKUP2()) & =VLOOKUP1()*VLOOKUP2()/VLOOKUP3().

    Hope that helps,

    Smitty

    EDIT: As for your first question, if you've got that much data you might consider using Access.

  5. #5
    Join Date
    Mar 2004
    Posts
    361
    Well here is my problem know. I have a worksheet with 6 columns. I need to get data out of the A column and the D column. I have written two different vlookup functions for that. Read as follows

    =VLOOKUP(W129,Color!D:F,3,FALSE)
    =VLOOKUP(W12,Color!A:C,3,FALSE)

    Some of the values are in Column A and some are in Column D. So is there a way to write a function that merges these? It would save me a lot of work if I could drag the formula through all of my numbers instead of having to go through each one.

    Thanks

  6. #6
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Are you pulling numbers or text?

    If you're trying to concatenate the information you can use:

    =VLOOKUP(W129,Color!D:F,3,FALSE)&" "&VLOOKUP(W12,Color!A:C,3,FALSE) - Will return both matches with a space in between

    or

    =VLOOKUP(W129,Color!D:F,3,FALSE)&VLOOKUP(W12,Color !A:C,3,FALSE) - No space

    Note that there's no error handling in there, so if you have blank information in either table, you'll need to add some. You can use IF(ISNA or use a range qualifier. i.e. =IF(SearchRange="","",VLOOKUP1())

    HTH,

    Smitty

  7. #7
    Join Date
    Mar 2004
    Posts
    361
    I tried both of them and both returned the dreaded #N/A?

    So here is what I typed in for the 4515 row in my sheet

    =VLOOKUP(W4315,Color!D:F,3,FALSE)&VLOOKUP(W4315,Co lor!A:C,3,FALSE)

    Any clue?

  8. #8
    Join Date
    Mar 2004
    Posts
    361
    Now I get the #Value error.

    my kingdom for an answer!

  9. #9
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Post an example of your sheets and I/we'll take a look.

    Smitty

    P.S. In the future it helps to keep relevant topics in the same post - that way everyone can follow along with your what's going on.

  10. #10
    Join Date
    Mar 2004
    Posts
    361
    Here is an example of what I am trying to do.
    Attached Files Attached Files

  11. #11
    Join Date
    Dec 2003
    Location
    San Diego, CA
    Posts
    153
    Try this:

    =IF(ISNA(VLOOKUP(A1,Color!A:C,3,FALSE)),VLOOKUP(A1 ,Color!D:F,3,FALSE),IF(ISNA(VLOOKUP(A1,Color!D:F,3 ,FALSE)),VLOOKUP(A1,Color!A:C,3,FALSE)))

    Or:

    =IF(ISNA(VLOOKUP(A1,Color!A:C,COLUMN()-1,FALSE)),VLOOKUP(A1,Color!D:F,COLUMN()-1,FALSE),IF(ISNA(VLOOKUP(A1,Color!D:F,COLUMN()-1,FALSE)),VLOOKUP(A1,Color!A:C,COLUMN()-1,FALSE)))

    Both worked just fine for me based on your example.

    Smitty

  12. #12
    Join Date
    Mar 2004
    Posts
    361
    Thanks a bunch!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •