PDA

View Full Version : Stupid MS Excel question


baz67
06-23-2008, 05:13 AM
I use Exel to track progress of installation by changing the cell color from no color to yellow to green. I want to be able to find out what percentage of cells in a row is each color. Can that be done and if so how? I use 2007.
6'9"Witha69
06-23-2008, 08:38 AM
You should use a status column with conditional formatting. Then use a countif function or an array to summarize them at the bottom or a pivot table if you need a little more summary type info.
BonzoHansen
06-23-2008, 11:29 AM
Yeah, you won't be able to count formatting (ie count the red cells), but you can drive formatting with values, as mentioned.
rocketrod
06-23-2008, 02:54 PM
Yeah, you won't be able to count formatting (ie count the red cells), but you can drive formatting with values, as mentioned.
It can be done, but there are more elegant and MUCH easier ways to do this.

baz67,

It would be helpful if you posted a sample spreadsheet with some additional details.
6'9"Witha69
06-23-2008, 04:23 PM
It can be done, Through vba or native Excel tools?
rocketrod
06-23-2008, 04:56 PM
Through vba or native Excel tools?
Using vba...
BonzoHansen
06-24-2008, 05:24 AM
Using vba...
Yeah, but thta is more trouble then it is worth, lol
6'9"Witha69
06-24-2008, 08:58 AM
Using vba...Thought so.

VBA is great for many really advanced spreadsheets especially for froms used in large companies (i.e. order forms) but for a car guys check list, status log, etc then native functions using a status column are much easier and offer greater versatility to the novice and intermediate user. Hell, I am an advanced MS Office user and prefer using columns. THen again, I am an IT guy, so I think in cloumnxrow layout all the time anyway.
rocketrod
06-24-2008, 02:06 PM
Yeah, but thta is more trouble then it is worth, lol
Definitely more trouble than its worth...


Thought so.

VBA is great for many really advanced spreadsheets especially for froms used in large companies (i.e. order forms) but for a car guys check list, status log, etc then native functions using a status column are much easier and offer greater versatility to the novice and intermediate user. Hell, I am an advanced MS Office user and prefer using columns. THen again, I am an IT guy, so I think in cloumnxrow layout all the time anyway.
VBA is not the way to go here at all, but even simple spreadsheets can benefit from the use VBA using macros.
6'9"Witha69
06-24-2008, 03:05 PM
Don't get me wrong, I last freehanded VBA for an Excel process last week, I just think it is overkill in a lot of situations and desperately needed in others. A lot of people just don't use it practically. Like MS Access and business users. "Look, I can create tables and queries, I'm now a DBA and app developer!":bsjerk:

Sorry, that was a bit of a vent.
rocketrod
06-24-2008, 05:09 PM
Don't get me wrong, I last freehanded VBA for an Excel process last week, I just think it is overkill in a lot of situations and desperately needed in others. A lot of people just don't use it practically. Like MS Access and business users. "Look, I can create tables and queries, I'm now a DBA and app developer!":bsjerk:

Sorry, that was a bit of a vent.
I hear ya!! I was Visual C++ developer for about 6 years, among other languages, and it is just scary what people cobble together using MS Access/VB/VBA/Excel etc.