Any workaround if database exceeds 5000 rows and need vlookup function from other sheets

Vivien Chong

If I need a database page where other sheets can pull information from it, to standardise data input, but having the following criteria/situation:

1) database page will keep information for 1 year (database = employee data)

2) will exceeds 5000 rows / 25,000 cells within the 1 year duration

3) cannot move row away as other sheets are pulling information from database page

 

Is there any workaround for this?

 

Thanks.

Paul Newcome

The best thing I can recommend is to find a way to break down the data such as A - E on one sheet, F - L on another, so on and so forth. Then in the formulas used to pull, you would write in logic that says if the last name begins with "letter", look on sheet one, if it begins with "letter", look on sheet two, etc.

.

Otherwise there is no way to exceed the limits without somehow breaking it out.

Vivien Chong

Hi Paul

Thanks for your suggestion.  I am interested to find out more about the formula that you suggested.

We are able to use formula to look into different sheet to pull information to the destination sheet?  I don't want to cell link, but need to use vlookup.

I would like to understand more.

Paul Newcome

HERE is some instruction on how to reference other sheets within a formula. The example formula they use is a VLOOKUP, so that should be able to provide some pretty good insight as to how to build the basics of your formula.

.

Expanding on my solution would require more details as to how exactly you are set up and how exactly you are planning to use data.

 

The biggest question would be... What is the GREATEST number of rows you anticipate having at any given time?

 

The next question set would be... What data will you be using as your search criteria, and are you able to break that down into sections?

.

EDIT:

Thank you, Andree, for pointing out my glaring mistake of not providing the link... Ugh. It's been one of those weeks already.

 

>>>>>HERE HERE HERE<<<<< is the link. Hahaha

Andrée Starå

Paul,

You forgot the link. 

Paul Newcome

Thanks. Ugh. I edited to add.

 

That looks like my morning routine of deciding whether or not I really want to open the door in front of me and go in to work. Haha

Andrée Starå

Sure thing!

Haha!

Vivien Chong

HI Paul

Thanks a lot.  I have read through the article.  But it didn't really mention how we can vlookup from multiple sheet.  I am not very good in formula and not sure how can I vlookup into multiple sheets.

Greatest number of row anticipated could be up to 2500, since we have 10 columns, the maximum capacity is 25,000 cells.

 

If I am to break down the master database to few sheets, eg, 

MD1: 1 - 1000

MD2: 1001 - 2000

MD3: 2001 - 3000

MD4: 3001 - 4000

 

In my destination sheet, I will have to use a formula to search for example:

Staff ID | Staff Name | Branch | Department

- When I key in Staff ID, it will pull the relevant Staff Name, Branch, Department from the respective master database.

 

If the staff ID that is keyed in is 1057, what will the formula look like that will search through MD1 - MD4 for the match?

Paul Newcome

It looks like you are not going to actually exceed the limit, so you should be able to keep it all in one sheet. If you wanted to break it down just to be on the safe side...

 

Follow the steps in the article to write the VLOOKUP to look at the first sheet. Then write your VLOOKUP for the second sheet. Then the third sheet. Then the fourth sheet.

 

Once you have those built out, you can use a nested IF to look at the ID and determine which sheet to reference.

.

=IF([Staff ID]@row <= 1000, first vlookup, IF([Staff ID]@row <= 2000, second vlookup, IF([Staff ID]@row <= 3000, third vlookup, fourth vlookup)))

Vivien Chong

Thanks Paul.  I'll try to work out on your suggested formula.

For my example above, I just simply put it to 1000 rows per sheet, the total rows had already exceeded 5000 for the master database now.  We are thinking how best to segregate the list.

Anyway thanks, will try it out and hopefully it can do what it needed to do.

With all the vlookup limitations, we really have to think of many workaround to get things done.

Paul Newcome

Happy to help!