DigitalZugzwang
New Member
- Joined
- Jul 27, 2017
- Messages
- 20
I have a workbook with a number of tables for dependent lists. Below is the formula for the third dependent list and it works for the most part, but I am getting strange results. When certain values are selected in column B the problem part of the below formula will result in #N/A or the wrong row. If I change the value in the table that is giving me a problem the code will work if it "likes" the new value.
For example, if I put in the word Flag Pole I get the #N/A but if I put in Test it will result in 3 when it should have been 5. I have no idea what is going on here. PLEASE HELP!
INDIRECT(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")),MATCH(B23,INDIRECT(SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"["&SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"]",0)),2))
Below is the portion of the formula that is acting up.
MATCH(B23,INDIRECT(SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"["&SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"]",0))
Below are two of the tables I'm using Activity (1 column) and Erection (2 columns)
<tbody>
</tbody><colgroup><col></colgroup>
For example, if I put in the word Flag Pole I get the #N/A but if I put in Test it will result in 3 when it should have been 5. I have no idea what is going on here. PLEASE HELP!
INDIRECT(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")),MATCH(B23,INDIRECT(SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"["&SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"]",0)),2))
Below is the portion of the formula that is acting up.
MATCH(B23,INDIRECT(SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"["&SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"]",0))
Below are two of the tables I'm using Activity (1 column) and Erection (2 columns)
<tbody> </tbody> | ||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||
<tbody>
</tbody><colgroup><col></colgroup>