Is there a limit to how many rows a function can search, for an IF statement? Having data not show.
For a requisition database, I maintain a pool sheet of available equipment, as well as sheets for orders and the vendor. I have formulas in a different sheet to add the VINs (let's say 5) that I am planning to add and it checks across the other sheets if that VIN exists. On this "check" sheet, I've noticed some VINs will populate the data as expected and others won't. However, when I manually go and check the other sheets, those VINs and the data that should be coming through exist, but it's not reflecting in my "check" sheet. Any ideas why this is happening? This is not the first time either. I've exported data before and information was not reflecting on the Excel, even though the sheet the data was being pulled from, did reflect accurately. I let the data flow if I'm adding a lot to give it time to work, but if I add 20 rows and 19 of them populate, and the 1 that isn't is in the table in between those 19, makes me wonder if there's another issue.
Answers
-
@MATF12 What's your formula look like?
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Afternoon Darren,
Formula is:
=IFERROR(INDEX({Dycom Pool Order Tracker Range 3}, MATCH(VIN@row, {Dycom Pool Order Tracker Range 2}, 0)), 0)
Though not sure why formula would be issue, it's set for the whole column. The data for the rows it did show, is the same data type, and the same status. Ex: row 1 showing status "Requisitioned", row 2 showing "0", but on sheet information is being pulled for, both rows show status "Requisitioned." I then checked VIN to make sure it's correctly identifying it, and both VINs were the only ones in that column. The above formulas is used to pull information into 1 column, I have 2 other similar functions with just the different column swapped in. Since the VIN is the key to getting data, that's why I checked VIN. But it's correct on both, so no idea why data doesn't flow. It's the same data as the other rows that were displaying correctly.
-
What error message do you get when you remove the IFERROR portion?
=INDEX({Dycom Pool Order Tracker Range 3}, MATCH(VIN@row, {Dycom Pool Order Tracker Range 2}, 0))
Do you have some VINs that are all numbers and other VINs that have letters mixed in?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It's the hash not available or blank, and all VINs are mixed.
-
I'm not sure I have seen the "not available" error message. Are you able to provide a screenshot of that?
And to clarify "all VINs are mixed"... Do you mean that all VINs are a combination of letters and numbers so that you have no VINs that are just all numbers?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Apologies, the NA one is my IF statement, forgot I myself write those in. It's the #NO MATCH message. And correct, all VINs for this equipment is a combination of letters and numbers only.
-
Ok. Understood.
And do you have screenshots that will show a row that SHOULD have a match but is throwing the error along with the corresponding match in the other sheet?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!