hide cell value if another cell is blank

[annadinesh]

i want that

the cell value on Range B6:B30 should show as per the value of D6:D30

like if a cell (D16-contains formula) is blank then the cell value of same Row i.e. (B16-contains formula) will hide before click Whatsapp Button


and so on..

using EXCEL 2019

Service CalculatorTEST.xls
	B	C	D	E	F	G	H
2	ESTIMATE
3			EON			20.06.2024
4	Parts Description				Labour Description
5	Name		Amount		Name	Amount
6	Engine Oil	1	925.00		PMS Charge	1702.74
7	Engine Oil Filter	1	107.00		Wheel Alignment	418.90
8	Air Filter	1	168.00		Wheel Balancing	424.80
9	Ac Filter	1	410.00		Engine Dressing	352.82
10	Coolant	1	400.00		3M Interior Polish
11	Distilled Water	1	50.00		3M Exterior Polish
12	W/screen Washer	1	20.00		Teflon Coating (1 time)
13	Bio Shampoo	1	11.00		Teflon Coating (3 time)
14	Engine Flush	1	770.00		Clutch O/H
15	Engine Additive	1	780.00		PMS Without Washing
16	Clutch Plate	1			Only Washing
17	Pressure Plate	1			Disc Facing
18	Release Bearing	1			Lathe- Wheel Bearing
19	Gear Oil	1			Lathe- Rear Axle Bush
20	Clutch Master	1
21	Clutch Release	1
22	Brake Pads Front	1			Additional Parts & Labour
23	Brake Shoe/ Pad Rear	1
24	Wiper Blade Front	1
25	Wiper Blade Rear	1
26	Spark Plugs	1
27	Fan Belt	1
28	Wheel Cylinders	1
29
30
31	* The Estimated amount may vary with the final invoice, subject to the job carried out and the cost of parts
32	TOTAL PARTS		3641.00		TOTAL LABOUR (incl Tax)	2899.26
33	GRAND TOTAL (Approx.)				6540.26
Quotation

Cell Formulas
Range	Formula
G3	G3	=TODAY()
F6:F21	F6	=PMS!A31
G6:G21	G6	=IF(H6,HLOOKUP($D$3,PMS!$A$3:$AD$50,ROWS($2:29)+1,0)*1.18,"")
B6:B29	B6	=PMS!A4
D6:D29	D6	=IF(E6,HLOOKUP($D$3,PMS!$A$3:$AD$50,ROWS($2:2)+1,0)*C6,"")
D32	D32	=SUM(D6:D29)
G32	G32	=SUM(G6:G30)
F33	F33	=D32+G32

Named Ranges
Name	Refers To	Cells
MODELS	=PMS!$A$3:$AD$3	G6:G21, D6:D29
Quotation	=Quotation!$B$2:$H$33	G6:G21, D6:D29

Cells with Data Validation
Cell	Allow	Criteria
D3:F3	List	=MODELS

 

[Joe4]

Two different ways:

1. Update formulas in cells B6:B29
If the formula in B6 is currently:

=PMS!A4

change it to:

=IF(D16="","",PMS!A4)

and copy down for all rows

2. Use Conditional Formatting on cells B6:B29
Select cells B6:B29, go to Conditional Formatting, choose the formula option and enter in this formula:

=$D6=""

and then you can choose the Format of white text, which will make the text in cell B16 blend in with the background and not be visible.

 

[annadinesh]

Two different ways:

1. Update formulas in cells B6:B29
If the formula in B6 is currently:

=PMS!A4

change it to:

=IF(D16="","",PMS!A4)

and copy down for all rows

2. Use Conditional Formatting on cells B6:B29
Select cells B6:B29, go to Conditional Formatting, choose the formula option and enter in this formula:

=$D6=""

and then you can choose the Format of white text, which will make the text in cell B16 blend in with the background and not be visible.

Thanks for the reply, but i want the value of B6:B30 should hide before pressing Whatsapp Button if cell value of D6:D30 is blank

 

[Joe4]

What do you mean by "hide"?
Do you mean hide the whole row, or just make it so the value in column B is not visible?

Note that you CANNOT physically hide column B without hiding the entire row (meaning ALL columns in that row will be hidden).
You can only hide entire rows in Excel, not just certain columns!

Also, in order to hide rows, you would either need to use Filters or VBA. You cannot hide rows just by using formulas.

 

[annadinesh]

What do you mean by "hide"?
Do you mean hide the whole row, or just make it so the value in column B is not visible?

Note that you CANNOT physically hide column B without hiding the entire row (meaning ALL columns in that row will be hidden).
You can only hide entire rows in Excel, not just certain columns!

Also, in order to hide rows, you would either need to use Filters or VBA. You cannot hide rows just by using formulas.

Only cell Value of B6:B30 if cell value of same row of D6:D30 is Blank

 

[Joe4]

Only cell Value of B6:B30 if cell value of same row of D6:D30 is Blank

It is still not entirely clear what you mean by that.
Let's work from an example. Let's say that cell D9 is blank.
So which of the two following options do you want?

Option 1:
Just blank out the value in cell B9, i.e.

Option 2:
Hide row 9, i.e.

The potential issue with the second option is there is no way to hide just column B and not columns F and H also.
You cannot hide just certain columns in Excel, it is hide the whole row, or nothing at all!
So, "Engine Dressing" would also be hidden, even though it is not zero!

 

[annadinesh]

It is still not entirely clear what you mean by that.
Let's work from an example. Let's say that cell D9 is blank.
So which of the two following options do you want?

Option 1:
Just blank out the value in cell B9, i.e.
View attachment 112981

Option 2:
Hide row 9, i.e.
View attachment 112984

The potential issue with the second option is there is no way to hide just column B and not columns F and H also.
You cannot hide just certain columns in Excel, it is hide the whole row, or nothing at all!
So, "Engine Dressing" would also be hidden, even though it is not zero!

Its Like Option 1

if there is no value on D9 then the Value of B9 should hide but only before we click on a command button

like as per my sheet there is value on D6 to D16 and D17 to D30 are Blank, we need only the value of B17 to B30 should hide not row and so on
if D17to D30 have value then the value of B17 to B30 should show.

 

[Joe4]

if there is no value on D9 then the Value of B9 should hide but only before we click on a command button

So, are you saying that BEFORE you click the command button, those values should be hidden, but AFTER you click it, the should be visible?
Is it possible that the button will ever be clicked more than once per file? Even if the same file is re-opened the next day?
What should happen if they click it more than once?

 

[annadinesh]

So, are you saying that BEFORE you click the command button, those values should be hidden, but AFTER you click it, the should be visible?
Is it possible that the button will ever be clicked more than once per file? Even if the same file is re-opened the next day?
What should happen if they click it more than once?

I am using 2 command buttons 1 is for sending whatsapp and another is for reset

so, if i click on whatsapp button then it should hide the value as mentioned above and when we click on reset button it return to the normal state

 

[Joe4]

I would recommend having your button update some unused cell on your sheet that is out of view, maybe something like AZ1. You could have button set it to “Hide”, and then the other button set it to “Unhide”.

Then you could use that in the formula I gave above, i.e.
=IF(AND(D16=“”,$AZ$1=“Hide”),””,PMS!A4)