Solved

Excel formula to evaluate visible cells only

Posted on 2014-04-10
10
1,168 Views
Last Modified: 2014-04-11
Hello Experts.
I need your assistance.  I use the following formula to evaluate group description in column A of the cell above to determine if it is the same as the group description in current row.  If it is, assign value in column B of above row to current row.  If not equal, evaluate if column B cell above is a 1 or 2 and assign the opposite.

B3=IF(A3=A2,B2,IF(B2=1,2,1))

Example:
A      B
Car      1
Car      1
Car      1
Bike      2
Bike      2
Boat      1

I than use column B to setup conditional formatting that would highlight each group of 2’s to easily see when a grouping changed.

The problem is when filters are used the conditional formatting does not work correctly.  When Bike is filtered out the highlighting doesn't change as the non-visible cells are still being evaluated in the current formula.

Example of issue:
A      B
Car      1
Car      1
Car      1
Boat      1
 
Is there something I can add to the above formula that would only evaluate the cells visible?

Thank you for taking a look at my issue.  Hope you have a solution
0
Comment
Question by:dhemple
  • 5
  • 3
  • 2
10 Comments
 

Author Comment

by:dhemple
ID: 39992134
I'm getting the sense that this might not be possible??
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39993299
When Bike is filtered out the highlighting doesn't change as the non-visible cells are still being evaluated in the current formula.
I'm guessing you're using something like =B2=2 in your conditional formating to highlight the cell. When you filter out bike, both bike entries will hide and you should not see any yellow. Your result should be as you listed above. It works in the attached. Were you expecting a different result?

Flyster
VisibleCells.xlsx
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39993444
Or are you looking for Boat to change to 2 once the filter is applied? If so, I'm still working on that one!
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39993447
Hi,

You could do it with a User Defined Function

Function ToggleVisibleCells(CellToCompare As Range) As Boolean
Set myCell = Application.Caller
Idx = 0
Do
    Idx = Idx + 1
    Set myCellOffset = myCell.Offset(-Idx, 0)
    IsCellHidden = myCellOffset.Height = 0
Loop Until IsCellHidden = False

If CellToCompare.Offset(-Idx, 0) = CellToCompare Then
    ToggleVisibleCells = myCell.Offset(-Idx, 0)
Else
    ToggleVisibleCells = Not (myCell.Offset(-Idx, 0))
End If
End Function

Open in new window

Regards
VisibleCellsV1.xlsm
0
 

Author Comment

by:dhemple
ID: 39993836
Hello Flyster and Rgonzo and thank you both for taking a look at my issue.

Flyster, I'm looking for Boat to change to 2 once the filter is applied.  The conditional formating would than highlight any rows that contain a 2 in column B.  Conditinal formating formula is "=$B2=2".  The conditinal formating works.

Rgonzo, I'm not sure I fully understand the code you have provided, but it appears that it would control cell visibility.  I'm not seeing how this would help me with the conditional formating of the filtered data.  Code language is not my strength...  Would you please walk me through your thoughts on this will help.

Thank you both for taking a look at this.

It would be very helpful if a solution could be found.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39993857
Hi,

Have adapted the conditional formatting

pls try

Regards
VisibleCellsV2.xlsm
0
 

Author Comment

by:dhemple
ID: 39993864
Thank you for your quick response.  You clearly understand what I'm trying to achieve.

I've tried the example you supplied and it works when I filter out "Boat", but it does not work correctly if I filter out "Car".

Any thoughts?
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39993885
In the mean time it is a new file
pls try

Regards
VisibleCellsV3.xlsm
0
 

Author Comment

by:dhemple
ID: 39993905
Rgonzo thank you so much!!!!!  You are awesome.....  it works!!!   There's no need for me to evaluate if row is a "1" or "2", as the code creates a TRUE or FALSE which is used in the conditional formating.

Cannot thank you enough.  Thank you!!!
0
 

Author Closing Comment

by:dhemple
ID: 39993907
This was a brain killer for me and Rgonzo made it seem so simple.  Thank you Rgonzo!!!!  you are awesome
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now