• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index+Sumproduct should return no value when you feed incorrect information, but return incorrect value instead.

Alientcp

New Member
Hello everyone. I am not as proficient at excel, but i try my best, hope you can help me with this issue.

Basically, i have 3 databases. The second one are routes a driver has to stop by, but each route has x amount of subroutes. The third one is the list of the bosses of each route (not relevant to the formula and already taken care of, just to illustrate what i want to do).


The first database, ill manually capture it with a userform (already working). The first field ill capture is the subroute. The second field is supposed to read the first one, search it in the arrays of the routes and return me the index. So, for instance, if in the first field i type "4" (check photo), the second field will return "Route 2", and so forth. Another cell will read the second field and return me the name of the boss.


The issue is, that if i dont enter any value yet, and/or if the first cell is blank, the second one, the one with the formula, will give me a default value of "Route 1" (and a boss on another cell, because the original cell is not blank).
I added an if statement that apparently works. (and gives me no boss)
If i enter a correct value of 1, ,2 or 3, it returns the correct value "Route 1" (after i added the if statement/ originally blank cell).
If i enter a correct value, such as "4", or "8", the cell correctly updates to either "Route 2" or "Route 3"
So, in essence, the formula works
But, if i add a value that it is not correct, any number not in the list, it still defaults me to "Route 1"
Or, if i enter a correct value, it returns a correct response, but if i change to something wrong, it defaults me to "Route 1" (i have to add that when i originally did this, any number i typed gave me the default "route 1" because the subroutes cells were "general" instead of numbers so, if i enter an incorrect value, it will probably be because of the same issue)

So, only clears if i delete the first cell value. But its not useful as is, if i capture correctly, its fine, but if i fat fingered during capture on the userform, ill not detect it and the missing fields will return incorrect information.

The formula
"=(INDEX(Route!$B$2:$D$2,,SUMPRODUCT((Route!$B$3:$D$8=Capture!A5)*(COLUMN(Route!$B$2:$D$2)-COLUMN(Route!$A$2)))))" No if statement.
"=IF(A5="","",(INDEX(Route!$B$2:$D$2,,SUMPRODUCT((Route!$B$3:$D$8=Capture!A5)*(COLUMN(Route!$B$2:$D$2)-COLUMN(Route!$A$2))))))" Hiding values if blank.

This is a small sample, it will be a huge database, with like 60 routes and at least 10 subroutes, and a lot of manual capturing.
But Capture= The first database that ill capture manually. A5 will be dynamic as i drag downwards.
Route = The Route database. The index titles are on the second, third and fourth file. So b2-c2-d2.

Edit. Added the workbook as is. I think i protected some cells, but it has no password. Just unlock it if it is.

Hope my doubt is clear. Thanks in advance for your help. I appreciate it.
 

Attachments

  • routes.PNG
    routes.PNG
    1.8 KB · Views: 4
  • Captura de capacitados.xlsm
    30.6 KB · Views: 5
Last edited:
Alientcp
Please reread Forum Rules
especially How to get the Best Results at Chandoo.org
After that, try to answer Yourself:
Could You figure Your challenge Yourself with Your sent snapshot?
... or would it be more clear with real sample Excel-file with used sample data as written in Forum Rules?
 
Alientcp
Please reread Forum Rules
especially How to get the Best Results at Chandoo.org
After that, try to answer Yourself:
Could You figure Your challenge Yourself with Your sent snapshot?
... or would it be more clear with real sample Excel-file with used sample data as written in Forum Rules?

English is not my first language, i think i gave a decent description.
The doc is in Spanish and its a mess because its on the design phase, and since its macro enabled, well, you know, may contain viruses (it doesnt, but i assumed it could be an unwritten rule or something), sure, i can upload it as is.
 
B5 copied down:
=IF(COUNTIF(Rutas!$B$3:$D$8,A5),INDEX(Rutas!$A$2:$D$2,0,MIN(IF(A5=Rutas!$B$3:$D$8,COLUMN(Rutas!$B$3:$D$8)))),"")
 
B5 copied down:
=IF(COUNTIF(Rutas!$B$3:$D$8,A5),INDEX(Rutas!$A$2:$D$2,0,MIN(IF(A5=Rutas!$B$3:$D$8,COLUMN(Rutas!$B$3:$D$8)))),"")
So sorry, i meant to reply yesterday, but i hitted review instead of "post reply" and did not checked, just closed the laptop and left

It does solve the issue of giving a result if you type something out of range, returns blank, as i want. But, if you do indeed add a correct value, it shows the first index only.

At least you pointed me to the right direction. Thanks for your time.
 
Back
Top