Data Bars Based on Text Values with Drop Down List

[PostTIT]

I have a table with one of the columns having a drop down list with 9 options. The drop down list takes it's options from a list elsewhere in the spreadsheet, as standard.

I understand how conditional formatting data bars works, using numbers in a range and filling out the bar based on the values. However, I want to have some magic done where it's not just values - but if I have to have values somewhere (and hide them away) then I will.


My list is the following: [TABLE="width: 75"]
<colgroup><col width="75" style="width:56pt"> </colgroup><tbody>[TR]
[TD="class: xl38, width: 75"]Not scanned[/TD]
[/TR]
[TR]
[TD="class: xl31"]Scanned[/TD]
[/TR]
[TR]
[TD="class: xl31"]Ready[/TD]
[/TR]
[TR]
[TD="class: xl31"]Submitted[/TD]
[/TR]
[TR]
[TD="class: xl31"]Processed[/TD]
[/TR]
[TR]
[TD="class: xl31"]Approved[/TD]
[/TR]
[TR]
[TD="class: xl31"]Paid[/TD]
[/TR]
[TR]
[TD="class: xl31"]Not Paid[/TD]
[/TR]
[TR]
[TD="class: xl31"]Denied[/TD]
[/TR]
</tbody>[/TABLE]

And so I would like the data bars to correspond to each word, where 'not scanned' is 0% through to 'Paid' is 100%, 'Not Paid' and 'Denied' sit at 90%. So that when each option is chosen from the drop down list the correct data bar is shown over it.

Would this be possible? Could you assign each word in that list a value (I wouldn't know how to do that) and therefore use conditional formatting to provide data bars that way?

I also then wouldn't be totally sure how to apply the conditional formatting to make the data bars appear dependant on which option is chosen.

Bit of a ramble, but if you can get through it and offer some help that would be great! In essence, data bars to be dependant on text....

 

[Mrex11]

Any solutions for this questions? Preferably using 365

 

[myall_blues]

Where do you want the data bar?
One way is to set up the validation list as two columns, one with the text value and one with the numeric value. Then use XLOOKUP to derive the value from the validation text, and format that value with a data bar.

Book1
	A	B	C	D	E	F	G
2
3	Not scanned					Not scanned	0
4	Scanned					Scanned	0.2
5	Ready					Ready	0.4
6	Submitted					Submitted	0.6
7	Processed					Processed	0.7
8	Approved					Approved	0.8
9	Paid					Paid	1
10	Not paid					Not paid	0.9
11	Denied					Denied	0.9
12
Sheet1

Cell Formulas
Range	Formula
B3:B11	B3	=XLOOKUP(A3,$F$3:$F$11,$G$3:$G$11,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B11,B13:B111	Other Type	DataBar		NO

Cells with Data Validation
Cell	Allow	Criteria
A3:A11	List	=$F$3:$F$11

 

[Mrex11]

Where do you want the data bar?
One way is to set up the validation list as two columns, one with the text value and one with the numeric value. Then use XLOOKUP to derive the value from the validation text, and format that value with a data bar.
View attachment 116659

Book1
	A	B	C	D	E	F	G
2
3	Not scanned					Not scanned	0
4	Scanned					Scanned	0.2
5	Ready					Ready	0.4
6	Submitted					Submitted	0.6
7	Processed					Processed	0.7
8	Approved					Approved	0.8
9	Paid					Paid	1
10	Not paid					Not paid	0.9
11	Denied					Denied	0.9
12
Sheet1

Cell Formulas
Range	Formula
B3:B11	B3	=XLOOKUP(A3,$F$3:$F$11,$G$3:$G$11,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B11,B13:B111	Other Type	DataBar		NO

Cells with Data Validation
Cell	Allow	Criteria
A3:A11	List	=$F$3:$F$11

I want the data bars to in the same column as the selection ( column a).

 

[myall_blues]

I don’t believe that would be possible without VBA.