#value

[azizrasul]

I have inherited a spreadsheet in which I have the following issue.

I have the following formula in cell AM7 and it's a range called Multiple_CP_Levels_Val

=INDEX(TopUp_Levels,Starting_TU_Level_Choice)

Cell Value in Cell
AM10 Yes
AM11 No

Cells AM10 to AM11 is called the range Multiple_CP_Levels but also TopUp_Levels.

Cell AM6 is Starting_TU_Level_Choice.

I have a form where if a check box is ticked, then I have

Cell Value in Cell
AM6 TRUE
AM7 Yes

but if the check box is unticked, then I have

Cell Value in Cell
AM6 FALSE
AM7 #VALUE

 

[Special-K99]

Try

=IFERROR(INDEX(TopUp_Levels,Starting_TU_Level_Choice),"")

 

[azizrasul]

That works by removing #VALUE and getting "" instead, but I really want is that if the check box is unticked, to have

Cell Value in Cell
AM6 FALSE
AM7 No

If I use

=IFERROR(INDEX(TopUp_Levels,Starting_TU_Level_Choice),"No")

then it works but is this the essence of how it should work, am I not 'fixing' it rather then the formula picking up the value of No from cell AM11 when check box is unticked just as it does when Yes appears when the check box is ticked.

 

[Special-K99]

Then you need to determine what gets set when the check box is ticked and check for the opposite.
You havent said what happens when the check box is ticked so I have to supply an alternative method.

For example when you tick the check box maybe A1 is set to 1, otherwise 0
Then it would be

IF(A1=0,"No",INDEX...)

So base something on this depending what the check box is doing when it's ticked.

 

[azizrasul]

Thanks Special-K99.