wonder if somebody could help,

cell B2 has a vlookup formula in it, I want to be able to type "Y" in C2 and the information displayed in B2 to be either colour blocked out or erased, keeping the formula in B2. when I remove the "Y" I would like the original information to be displayed in B2 again , is this possible ? failing this a check box in C2 maybe?


Maybe I'm misunderstanding, but you can just nest the VLOOKUP inside an IF

=IF(C2="Y", "", VLOOKUP(3, A1:A5, 1))

If true, show empty string, otherwise result from VLookup.

  • yes this has worked fine thankyou – adam. Jan 10 at 8:44
  • Glad to hear it. And welcome to Super User. If this answer or any other one solved your issue, please mark it as accepted. – BlueGI Jan 10 at 14:40
  • hi just have a slight amendment to the above , – adam. Jan 10 at 15:54
  • rather than vlookup in cell B2 I have now decided to use a data validation drop down list , when "Y" is type into C2 I need B2 to clear then when the "Y" is removed I need the original information of the data validation to be displayed . any ideas how this could be done ? – adam. Jan 10 at 15:58
  • Then I would use one of the other conditional formatting suggestions here to change the font color to hide it. Or better, create a new question with exactly what you want since it looks like it's gone beyond your original request. – BlueGI Jan 10 at 17:09

You could use a formula inside conditional formatting that will allow you to change the background colour or grey out text depending on whether C2 has a "Y" in it.

e.g Locatate Conditional formatting tab = choose more rules = choose "use a formula to determine which cells to format" = inside formula section put "=$C$2="Y"", then choose the formatting options.

  • thankyou for your help – adam. Jan 10 at 8:44

If create a helper column in column A you can use conditional formatting. 1) Select the column which contains your data. 2) Click on the conditional formatting button and select "new rule". 3) Select to use a formula to format the cells and enter =$A1="Y" in the formula bar. 4) Click on the format button and select to fill your cells black. This should block out the values if there is a Y in column A.

  • thankyou for your help – adam. Jan 10 at 8:44

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.