VLOOKUP Questions

Shawn R

I've got a basic handle on VLOOKUP but what I am trying to do is a little unconventional due to sheet row limitations within smartsheet.

We have a sheet in which people will fill in a part number, each part number has a part description. What I want to do is automate the part description field. So I want to use a VLOOKUP to reference other sheet to match the part number of the row and fill in the corresponding part description.

My issue starts with the fact that we have other 15,000 parts and part descriptions. So when I imported the data from excel into smartsheet I had to do so in 4 separate sheets due to the 5000 row limit Smartsheet has.

I cant seems to write a functioning VLOOKUP to search the part number field of all 4 sheets no matter how I configure the syntax of the VLOOKUP.

I've read on some documentation https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets?_ga=2.267643466.530744127.1534772995-1133749180.1518182414

This makes it seem possible, was hoping for some help.

Shawn R

As always I seem to find a solution shortly after posting the question. I was able to get my VLOOKUP to work when referencing multiple sheets using the =IF(ISERROR, followed by the 4 separate VLOOKUP formulas.

However having to reference two columns in 4 sheets that total over 15,000 rows means I exceeded the limit of referencing 25,000 cells by the time I reference the third sheet.

So it looks like the limitations of smartsheet are not going to allow me to automate this process.

J. Craig Williams

Shawn,

My post here does not imply I have solution, but ...

1. Are your part numbers ordered?

2. Are new ones being added? (How often?)

3. Is this only two columns of concern - [Part Number] and [Description]?

Craig

 

Shawn R

Craig,

1. Yes our part numbers are ordered. However, they start with an abbreviation to the customer name. Examples would be: ABC00001, ABC00002, XYZ00003, etc.

2. New ones can be added frequently or infrequently. It really all depends, if I had to give a rough average I would say maybe 1-2 a month.

3. Yes this is correct, there are only the [Part Number] and [Description] that is of concern.

J. Craig Williams

Thanks Shawn.

AppSheet released a feature in the past year that allows a large table to be broken up into smaller chunks and then references in their tool, which integrates well with Smartsheet.

I have not used it, as it did not meet my requirements, but it might meet yours.

I have a Smartsheet only solution percolating in the back of my head (I can feel the hairs standing up and my ears itch), but it is not yet fully formed. When/if it does, I'll post here.

Craig

Shawn R

Craig,

 

I will check out AppSheet here today at some point. I look forward to your potential Smartsheet solution if it does form. Thanks!