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.
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
Last edited: