excel trick? drop down list then other cells invisible, can't work out how made it

AmyZ

New Member
Joined
Jul 16, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Dear All

I come across a excel template which created few years ago, the person who created it has left the job. the template has a drop down list, you can select either subscription or purchase. the magic to me is, if you select purchase, some other cells text became invisible, the cells are still there, just you see blank cells. but if you click the cell, you will see the text in the formula bar. if you select subscription, you will see the whole set of cell text appeared again. I try to work out how it has been created for days and googles all possible solutions, no answer at all.

it's not a dependent drop down list. I also review if there is formula on those cells, nothing, just plain text

would be great if any of you contribute your thoughts as some of the cells does not work properly, i.e. when you select purchase, cells still visible. I need to amend the template and share it with other shortly.

Much appreciated your ideas.

Thanks
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
Hi AmyZ,

It's most likely Conditional Formatting which changes the font to white, hence making it invisible against a white cell background.
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,649
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
It sounds as though it's using conditional formatting to change the colour of the font.
 
Upvote 0

AmyZ

New Member
Joined
Jul 16, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
You guys are amaaaaaaaaasing!! ??Yes, it is conditional formatting! I can see the rules on conditional formatting. I am so pleased find this forums! I can't get any helps at workplaceo_O

There comes another question, there are more than 40 cells I need to amend the rules. I assume I have to manually correct each rules, is there other quicker way I can amend or not? I hope I have explained clearly what I am trying to do, but if there no other solution, I will manually amend each rule.......
Thanks again, you guys saved my life.
 
Upvote 0

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,549
Office Version
  1. 2016
Platform
  1. Windows
There comes another question, there are more than 40 cells I need to amend the rules. I assume I have to manually correct each rules, is there other quicker way I can amend or not?
You're welcome!

Apart from using Conditional Formatting (CF), Manage Rules I don't know of easier shortcuts to amending CF settings, but I will give a couple of tips:

One challenge I've seen is ending up with is Manage Rules showing almost duplicate settings but with slightly different "Applies to" ranges because a slightly different range was selected for one of the CFs or changes to a CF. To avoid that I select the range and use Formulas, Define Name to give it a name, such as zz. Then when I want to make a change I do Ctrl-G and enter zz so it selects the range again. You can also use this for non-contiguous cells by holding down Ctrl and left-clicking each cell you want and then Define Name.

If you're using formulae to decide the TRUE or FALSE for a CF then debugging it in the CF, Manage Rules can be tricky. I always use a grid off to one side to test the TRUE/FALSE first. In this case once I see it's working I just use the formula in J5 as the CF rule.

Cell Formulas
RangeFormula
J5:L11J5=AND($E5="Dog",F5>5)
Named Ranges
NameRefers ToCells
zz=Sheet1!$F$5:$H$11J5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F5:H11Expression=AND($E5="Dog",F5>5)textNO
 
Upvote 0

Forum statistics

Threads
1,186,777
Messages
5,959,724
Members
438,443
Latest member
azedin4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top