How can I make a cell "transparent" if a formula is FALSE?

[ihavenoidea]

A1 says "This sentence is too long", B1 has a formula.
When B1 is true, A1 is blank.
B1 has =IF(A2="Yes", 1, "")

My problem is, when B1 is false, it displays like this:
| This sentence is |___________|
( ____ being a blank B1)

If B1 is false, I would like it to continue to display the contents of A1 through B1, the way it would if B1 was completely blank. For the cell to be "transparent"

I guess my IF statement is returning "", which excel interprets as data. How can I make this work?

(changing cell widths is not an option, maybe changing the IF statement? conditional formatting?)

I'm using excel 2002. Thanks for your help!

[dcgrove]

If B1 contains a formula, why show it. i guess I am confused. Can you post a workbook with an example of what you are getting now, and an example of what you are trying to achieve

[ihavenoidea]

Sorry, maybe my question wasnt very clear. Attached is a workbook that illustrates my question better.

[dcgrove]

Sorry, I don't know of any way to accomplish what you are trying to do without some sort of VBA trickery.

[ihavenoidea]

Yeah i thought so. I've been trying for days now. It seems like a relatively simple idea, but with no simple solution (if any) in sight.

Maybe its too simple. haha.

Hopefully someone else can provide some insight?

[scaffdog845]

dcgrove

The obvious solution would be to widen A:A which you said is not an option. By default Excel would allow the lenghty contents of A1 to sipll over to B1 if it were inded blank.

Could the formula in B1 be moved over an appropriate number of columns to allow A1 to fully display?

There may be a VBA solution to this, however a restructuring of your layout would be the simpliest route.

If this is not possible one of the Gurus may have an idea....

Have a great New year!

[ihavenoidea]

Yeah, restructuring isn't really an option. I've boiled down the question to its essence, in order to simplify it for this forum. In reality, my spreadsheet is 8x20, with virtually every cell containing multiple IF and VLOOKUP statements, indexing 3000+ cells in the tables below.

At work we have a flip chart with all the part specifications which is somewhat outdated, im trying to replace our flip chart with this excel database. Where the user inputs the model # and all the specifications come up. My problem is that some parts have additional notes; its these overlapping notes that are giving me this problem.

A co-worker told me that this might be easier to do on Access, which is probably true, but im not very familiar with the program. I'd really like to accomplish this in Excel. It sounds so simple, but it just isn't for some reason. Any help is greatly appreciated!

[shg]

What you want isn't going to happen with our without VBA. It's just not how Excel renders to the screen.

[Cutter]

There is this option, although you may not want to fiddle around with shapes.

Insert a Rectangle over cells A1:C1 and have the contents of it = A1

[ihavenoidea]

That's exactly what I ended up doing. Formatted A1 as ;;; so it wouldn't display, and covered it with a transparent textbox (=A1) with no lines.

Had to mess around with conditional formatting a lot to get everything to work, specially since you're limited to only 3 conditions per cell. But it all worked out.