Forum Discussion
HELP! Formula or Conditional Formatting?
Hi! I am coming across a road block and it seems like what I am trying to do is simple enough, but everything I have tried does not work. I am wondering if maybe it's because there is already a formula but I have hidden with =IFNA.. yada yada. I will provide screenshots so you can see what I am trying to do. The examples I provide are just that.. examples for the sake of this exercise.
Example: Bit of Background: I sell booksets to bookstores across the US and Canada and I have 4 types of booksets. Book set 1 contains 2 books in a set; Vol. I & Vol. II. Book set 2 also contains 2 books in a set; Vol. III & Vol. IV. Book Set 3 contains the full set Vols. I-IV and finally Book Set 4 contains the same as Book Set 3 except the books are signed.
What I am trying to do: A book store in Canada bought 250 units of book set 1. I am creating an "invoice" however, for customs purposes, I have to list what is included in the set and their descriptions (Cells A6-A9). Now, I have VLookup formulas in those cells, including cells A10-A12 as I have other bookstores that purchase Bookset #3 and Bookset #4 but I have them hidden w/ IFNA formula. I also have simple formulas in cells B6, B8, B10, & B12 (=B2) See screenshots below.
How can I hide the quantities that are in B10 & B12 when A10 and A12 does not return a value? I have tried IF formulas, I have tried conditional formatting, and nothing I have tried worked so I am stumped. I know I can simply just highlight the cell and make it white but then I'd have to remember to undo that when I sell a book set that contains all 4 books and need the values that are in B10 & B12, if that makes any sense at all. More screenshots provided below
Any help at all would be much appreciated! Thanks!
Use only one of the following options:
Option 1: A formula. (Edited to correct mistakes)
Select B10 and enter the formula
=IF(A10="", "", B2)
Select B12 and enter the formula
=IF(A12="", "", B2)
Option 2: conditional formatting.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=A10=""
Click Format...
Activate the Number tab.
Select Custom in the Category list.Enter ;;; (three consecutive semicolons) in the Type box.
This will hide the contents of the cell.
Click OK, then click OK again.On the Home tab of the ribbon, in the Clipboard group, click the Format Painter.
Then click on B12 to copy the conditional formatting rule.
- Thank you for the reply. Unfortunately that did not work 😞 Basically, what I wanted was, when A10 is blank, I want B10 to be blank as well but I guess it may not work out the way I wanted.
Thank you anyway!!Sorry, my mistake. Option 1 should have been
Select B10 and enter the formula
=IF(A10="", "", B2)
Select B12 and enter the formula
=IF(A12="", "", B2)
