# Thread: Extend total # of rows in excel?

1. Registered User
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. Registered User
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.

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

4. Registered User
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. Registered User
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. Registered User
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. Registered User
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. Registered User
Join Date
Mar 2004
Posts
361
Now I get the #Value error.

9. Registered User
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. Registered User
Join Date
Mar 2004
Posts
361
Here is an example of what I am trying to do.

11. Registered User
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. Registered User
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
•