Hello friends, this time have another problem:
there is a sheet containing tons of information. I need to highlight only the smallest number for each row. I can't just simply use conditional formatting TOP 1 solution, because every second cell in a row contains some serial numbers... I hide them and try to apply conditional formatting only on visible cells, but formatting still uses hidden cell info... How to ignore hidden cells?
Something like this?
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Before posting here I did a google search and found that link, but either it is not the solution or either I am soft in the head today (have a flu)..
Simplest way I can think of:
copy only visible cells to another sheet and do the job, then just manualy highlight smallest cell on original sheet.
But I know that there is cooler and probably simplier solution.
Shread sheet looks like this:
A B C D
5 1 7 6
Where B and D are hidden so I need that only visible bottom cell would be highlighted. In this case A cell.
Be sure that best way to describe your problem is to upload a sample workbook.
Be sure that all sensitive data removed, showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that.
To attach a small sample workbook.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
OK please find attached simplified sheet.
I need to highlight lowest (or highest) value per ROW. While ignoring information in hidden cells, because they also contain numbers.
If i understand correct, the hidden columns have different header than visibles. Perhaps you can use the headers as reference.
This formula highlight with grey color only the empty cells of the visible columns..
Formula:
Please Login or Register to view this content.
It's difficult to use SUBTOTAL here as suggested in the link because SUBTOTAL only works to ignore hidden/filtered rows.....but you have hidden columns. As Fotis1991 says, you can use the fact that the columns that you want to use all have the same header, e.g. select the range D2:CR5 and then use this formula in conditional formatting
=AND(D2<>"",COUNTIFS($D2:$CR2,"<"&D2,$D$1:$CR$1,"Tarifas")=0)
set required format and that will highlight only the smallest value for each row in the Tarifas columns - this assumes that the other columns are hidden - you can have them visiable if you want but then you need to change the formula to this
=AND(D2<>"",D$1="Tarifas",COUNTIFS($D2:$CR2,">"&D2,$D$1:$CR$1,"Tarifas")=0)
To get the highest value just set another condition with the same formula but with < changed to >
see attached - low values in yellow, high in red
Last edited by daddylonglegs; 02-24-2014 at 08:33 AM.
Audere est facere
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks