Hello all,
I am working with a somewhat complicated formula that seems to work when referencing cell values with text, but not when cell values are numbers:
I'm unable to use the function button to work the formula piece by piece because the data is volatile but I found out that as stated above, when $B$17 is a * or contains text, I am given the correct cell results. When I change $B$17 to a "1" for example, I'm given #N/A instead of the correct results.
I'm using Excel 2013 on Windows 10. Do you know the solution to getting this formula to work properly with numbers?
I figured it out. For those searching, we have to tell excel that even if it finds a cell reference as a number, we want it to see it as text. Here's the fixed code:
I am working with a somewhat complicated formula that seems to work when referencing cell values with text, but not when cell values are numbers:
Code:
=INDEX(INDIRECT("'"&C$6&$D$2&"'!"&"$B$5:$I$15"),MATCH($B18,INDIRECT("'"&C$6&$D$2&"'!"&"$A$5:$A$15"),0),MATCH([B][COLOR=#ff0000]$B$17[/COLOR][/B],INDIRECT("'"&C$6&$D$2&"'!"&"B4:I4"),0))I'm unable to use the function button to work the formula piece by piece because the data is volatile but I found out that as stated above, when $B$17 is a * or contains text, I am given the correct cell results. When I change $B$17 to a "1" for example, I'm given #N/A instead of the correct results.
I'm using Excel 2013 on Windows 10. Do you know the solution to getting this formula to work properly with numbers?
I figured it out. For those searching, we have to tell excel that even if it finds a cell reference as a number, we want it to see it as text. Here's the fixed code:
Code:
=INDEX(INDIRECT("'"&C$6&$D$2&"'!"&"$B$5:$I$15"),MATCH($B18,INDIRECT("'"&C$6&$D$2&"'!"&"$A$5:$A$15"),0),MATCH(TEXT($B$17,"0"),INDIRECT("'"&C$6&$D$2&"'!"&"B4:I4"),0))
