Excel Conditional Formatting 2

[sticksandtriangles]

I am an excel beginner and wanted to know if it was possible to conditional format cells based on a drop down list.

For example, I have an input form that looks like this with drop down activities:

I have another sheet that has sample data formats like this (each cell has a specific custom formatted data assigned to it):

Ideally I want to conditionally format my cells to that if "walking" is selected in the 1st sheet, the data input is formatted based on the sample data format of the 2nd sheet.

Hopefully that makes sense. I played around with conditional formatting, but it seems like I need my format button to be able to be programmable with a vlookup type function.

In the end, I am going to have a whole bunch of activities in the list and I really want the input form to be dynamic based on the sample data format of the 2nd sheet.

Thanks!

S&T

 

[3DDave]

Conditional formatting applies formats to cells based on conditions. They don't dynamically create formats.

 

[sticksandtriangles]

3DDave, I think we are the same page here, but just to be clear when you say:

Conditional formatting applies formats to cells based on conditions. They don't dynamically create formats.

My formatting has already been created on the second sheet, I really am only trying to apply formats to cells based on conditions, its just the formatting has to be conditional as well.

In the end, I think we are on the same page, but it is just not possible with excel.

S&T

 

[3DDave]

Look at the rules that conditional formatting operates with.

 

[SkipVought]

Your Format cannot be copied from anywhere. The Format must be configured in the CF Wizard.

The Formula either returns True or False. If True, the the specified Format is applied.

You can configure a Number Format like this..
[tt]
Formula: =B$1="Walking"
Format>NumberFormat>Custom: General "steps"

Formula: =OR(B$1="Running", B$1="Biking", B$1="Mountain Biking")
Format>NumberFormat>Custom: General "miles"
[/tt]

Oh, BTW, you must SELECT the entire range you want to CF BEFORE you open the CF Wizard. Your CF Formula must be written with respect to the UPPER-LEFT cell in the CF range. Hence, a reference to B$1.

Another observation, the format of your table will restrict you in at least two ways.
1. Your CF will constantly need to be adjusted to the right as you add new activities
2. You might decide you want to add the Time of the activity at some point in the future or some other data element.

The structure of your sheet is really a report summary. I would NEVER store data in a report summary structure!

Your table ought to be something like...
[pre]
Activity ActDate ActValue ActUnits
Walking 8/25/20 1800 Steps
Running 8/26/20 25 Miles
[/pre]
...from which you could generate a PivotTable in a structure similar to your example, sans CF NumberFormat. BTW, the ActUnits CAN be a value from a Lookup, based on the value in Column A.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IRstuff]

Your output seems to want to be using the number format options and not CF, since you can add a suffix there

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[Agent666]

The format can be applied using vba based on worksheet change events tied to the drop down cell. Create a 'cell style' and apply that via vba to a range would be the logical way to do it if you couldn't get conditional formatting to do exactly what you want. Managing a central style vs numerous conditional formats would be easier as well if you wanted to change formats or add further ones in the future.

Otherwise you can presumably do it with a lot of individual conditional formats, one for each type. Just check that your drop down cell value equals 'something' and format number and cell styles accordingly to whatever 'something' means.

If your only meaning adding the 'steps' or others afterwards and no other formatting. Why not just use a concatenated formula and helper cell/column/row to enter your raw value, and concatenate it with your desired suffix via a xlookup?

 

[Agent666]

Your output seems to want to be using the number format options and not CF, since you can add a suffix there

You can actually change the number format via conditional formatting. Select number and custom, and entering #" steps" will achieve what is being asked I believe

 

[SkipVought]

Here's a workbook example of your CF, but not using your template.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[sticksandtriangles]

I appreciate the responses. I was hoping I would not have to maintain a large formula like shown by Skip (I plan to have a large amount of activities with lots of differing cell formats).

I have not worked in VBA before, I'll have to add that to my list of future learning.

S&T

 

[SkipVought]

If you want to attempt a VBA solution, here are some caveats.

[ul]
[li]When your workbook is opened, Excel will ask you to Enable macros, unless you are foolish enough to carte blanche allow macros. If macros are not enabled, the VBA code will not run.[/li]
[/ul]

[ul]

[li]Some companies or individuals do not allow macros to run.[/li]
[/ul]

[ul]
[li]Macros (VBA) can error under certain conditions, often referred to as "bugs." Programmers attempt to write their code to gracefully accommodate such error conditions. Often, help that you get here and other places, tends to disregard error condition processing. After all, what you're getting is free "tips" not a rock-solid, error-proof solution that a paid professional would provide and guarantee.[/li]
[/ul]

[ul]
[li]It takes time and effort to learn VBA. It was worth it for me. I was motivated because I could see the value it would return for my career. When I encountered sites like Eng-Tips and [link Www.tek-tips.com]Tek-Tips.com[/url], I received additional help learning and applying these new skills.[/li]
[/ul]

And there are many more like, now you have something else to understand maintain.

But it you choose to take on these challenges, the results, meaning the things you could perform via code, open new, exciting and unimaginable horizons. However, you really need to know and understand the native capabilities of Excel before you go charging into VBA, IMNSHO.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[hydrae]

For what conditional formatting is good for, watch the Standupmaths youtube.

https://www.youtube.com/watch?v=UBX2QQHlQ_I

Hydrae

 

[SkipVought]

That's okay, hydrae, if you really want to waste 13 minutes and 17 seconds of your life.

It has absolutely nothing to do with this thread except for the words "conditional formatting" and "Excel" and it will not enlighten you on anything of real value.

Of course, now you're gonna check it out and waste 13 minutes and 17 seconds of your life as well. O L.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IRstuff]

Oh, wasted WAY more than 13 minutes, particularly watching the domino computers.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm