Using Text as a Data source for formatting

Microsoft Excel 2007
August 27, 2009 at 17:29:00
Specs: Windows Vista
The issue im having is that im keeping a record of what computers repond to ghost LAN wakes/shutdowns and Columb A represents the computer name. I also want columb a to visually display for example Green is a neraly perfect responding computer, yellow is a iffy and red means there is something wrong with ghost

Im trying to avoid doing this manually because there are about 235 computers that are used in this spreadsheet

So is there a way to turn the text in a box to a numerical data, like for example 'Fail = 0' and 'Pass = 1' which then i can use as a percentage to auto assign the current machine status to computer A

Or would i be better off using Access to do this and if so where can i find a good site that shows detailed instructions on how to go about this?

Thankyou for your time

See More: Using Text as a Data source for formatting

Report •

August 27, 2009 at 18:34:15
re: is there a way to turn the text in a box to a numerical data

What do you mean by text in a box? Where is this Pass/Fail text being stored?

If it's in a cell, you could use an IF statement in another cell to return a 1 for Pass and 0 for Fail and then average those cells.

Once you have an average (or percentage) you could use 3 Conditional Formats conditions to turn the computer name (or any cell) Green, Yellow or Red based on the average or percentage.

But first, I think you need to tell us what you mean by text in a box.

Report •

August 27, 2009 at 19:50:51
the text that is in the cell. for example i just type in pass or fail in respect to the event

Report •

August 28, 2009 at 05:48:54
This may not be exactly what you are looking for, but see if the concept can be modified to fit your needs.

First, if I had to type Pass or Fail next to 235 computer names, the first thing I would do is automate the entry of the text.

Put these 2 macros in the VBA editor:

Sub PassText()
    Selection.FormulaR1C1 = "Pass"
End Sub

Sub FailText()
    Selection.FormulaR1C1 = "Fail"
End Sub

Then use Tools...Customize...Commands tab...Macros and drag a 2 Custom Buttons or Custom Menu Items up to a toolbar.

With the Customize dialog box still open, right click the item and edit one to say Pass and the other to say Fail.

Right click them again and choose Assign macro and assign each button the appropriate macro.

Close the Customize dialog box.

Now you can select any cell and click the appropriate button to put the text in the cell.

Next, now that you got a bunch of cells that say Pass or Fail you could use COUNTIF and COUNTA to create a percentage, e.g. in C1 put:


Once you have this number, use Conditional Formatting to color your cells, based on your percentage criteria,

e.g. using 30% and 70% as my breakpoints:

Condition 1...Formula Is...=C1<=.3

Condition 2...Formula Is ...=IF(AND(C1>.3,C1<.7),TRUE,FALSE)

Condition 3...Formula Is...=C1>=.7

I hope that points you in the right direction.

Report •

Related Solutions

August 30, 2009 at 19:33:01
Thanks, i didnt need to use the macro as i just use the drop+drag method for multiple pass/fails but the advice after that works perfect and its a easily visual representation of good->bad

Report •

Ask Question