Any workaround if database exceeds 5000 rows and need vlookup function from other sheets
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.
Comments

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.

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.

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

Paul,
You forgot the link.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

Sure thing!
Haha!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå  Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com  E:andree@workbold.com  P: +46 (0)  72  510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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?

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)))

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.

Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives