IF and condition not met then go to next row ?

[p45cal]

Sub blah()
'Set SceData = Sheets("CONSOLIDATE From CO's").Range("A1").CurrentRegion.Resize(, 29) 'old
Set SceData = Sheets("CONSOLIDATE From CO's").Range("A1").CurrentRegion.Resize(, 30) 'new
Set CritRng = Sheets("Control").Range("A1:A2")
CritRng.Cells(2).Value = "Complete"
'SceData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, CopyToRange:=Sheets("COMPLETE | GETTING PAID").Range("A1:AG1"), Unique:=False'old
SceData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, CopyToRange:=Sheets("COMPLETE | GETTING PAID").Range("A1:AD1"), Unique:=False 'new
CritRng.Cells(2).Value = "<>Complete"
SceData.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, CopyToRange:=Sheets("BN CAIP-SDAP TRACKER").Range("A1:AD1"), Unique:=False
End Sub

 

[rbercher]

Hi, p45cal,

I was wondering if you'd be willing to help me out again. I'm again at an impasse with some data that I need to consolidate automatically. I've attached the sheet I'm working on for you if you wouldn't mind helping me out again and I truly hope that I'm not bugging you too much.

I'm looking to do something similar to what you have above, but for multiple worksheets to a master worksheet. I've been going through several forums and tried several different methods of what I know to do, but to no avail. Below is what I'm looking to do:

1. Copy data with formatting from the first three sheets to the "CPB All" sheet, excluding the "Data" sheet.

Thanks again for all of your help with this.

 

[p45cal]

Copy data with formatting from the first three sheets to the "CPB All" sheet, excluding the "Data" sheet.

Will do this sometime today, but the 'with formatting' will get to be a problem with these sheets.
Most, if not all, the cells you want to copy over are formatted with conditional formatting only, and it looks like you've been busy with copy/pasting in the past, because on the two CYBN sheets and the CPB ALL sheets you have some 900 conditional formats!. It gets worse, on the HHC sheet you have an astonishing 41,190 conditional formats. This will ultimately slow your workbook down to a snail's pace.
I strongly recommend you clear them all out and start again and since the 4 sheets are all very similar, do it on one sheet then make copies of it and rename them. I would expect you only need a handful of conditional formats for the whole sheet.
I propose to write code to copy values (and number formats) only and let the destination sheet's existing conditional formatting do the formatting, that way there won't be any needless build-up of conditional formatting. If there's ever going to be some manual formatting that needs copying over you need to tell me.
If you steamline the conditional formatting for one sheet and attach it here I can make sure that it's as resource-light as possible.

 

[rbercher]

Would you happen to know if there is a way of error checking that highlights errors made or missing required information without conditional formatting? I know that too much of that stuff will make a worksheet laggy and not user-friendly and I want to try to avoid that as much as possible.

I'm currently working on what you advised I do as I didn't realize that all of those rules were copied so many times lol. What if I turned it into a table: Would that keep the conditional formatting in added and deleted rows in a dynamic manner or does it have to be applied manually to each row after it's added?

[Update] I attached the streamlined version as requested. The formatting is only in the HHC tab. At the least, it needs to be included on the next two tabs with no formatting on the "CPB All" tab. In order to get it to attach, however, I had to delete the "CPB All" tab which is something that we need.

I did some research and understand there is a way to make the "CPB All" tab through a macro and have the data from the other three tabs copy over. I guess that would be ideal and each time we run the macro, it would delete the "CPB All" tab and then make it new or simply write over all the data currently in it. Not sure which way would be the best. I just hope I cut down on all conditional formatting you mentioned.

 

[p45cal]

1. Macro which currently copies the 3 sheets of data to one sheet.
Currently it does not erase existing values, it adds to them. I'll sort that out later.
It copies across without conditional formatting and so will expect to see conditional formatting in the destination sheet.

Sub blah()
Dim RngSce As Range

For Each sht In Sheets(Array("HHC", "1ST CYBN", "2ND CYBN"))
  lr = Sheets("CPB All").Range("A2:C553").Find(what:="*", LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByRows, searchdirection:=xlPrevious, searchformat:=False).Row
  Set Destn = Sheets("CPB All").Cells(lr + 1, 1)
  Set RngSce = Nothing
  On Error Resume Next
  Set RngSce = sht.Range("A3:C553").SpecialCells(xlCellTypeConstants, 3)
  On Error GoTo 0
  If Not RngSce Is Nothing Then
    Set RngSce = Intersect(RngSce.EntireRow, sht.Range("A:AP"))
    RngSce.Copy
    Destn.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
  End If
Next sht
End Sub

2. Trying to reduce the conditional formatting on all these sheets to the minimum, I have a few questions and this is the first:
In the picture below, there's your conditional formatting (with shortened formulae) for some of the cells. The shaded ones can certainly all be put into a single conditional format, but I was wondering whether the two unshaded ones were intentionally slightly different?

 

[rbercher]

No, I think it was me either forgetting the "$" or omitted it on purpose. I don't think it will effect it much. I wanted to try to keep the formatting in that specific column with the "$", so if it's missing, chances are I did it on purpose.

 

[p45cal]

No, I think it was me either forgetting the "$" or omitted it on purpose. I don't think it will effect it much. I wanted to try to keep the formatting in that specific column with the "$", so if it's missing, chances are I did it on purpose.

Could you look a bit more carefully at the differences in the picture; it's not just a case of a missing/present $ symbol. You'll see that all the shaded ones refer to the column they're in. The unshaded ones don't (the last two rows in the picture have the same formula but apply the format in different columns).

A bit more explanation:
All the formats in the picture check for a name in the Name column (column C); if there's nothing in that column no formatting happens.
Now to look at the other part of the formulae. All the shaded ones say If there's a name in column C and there's nothing in me then highlight me
The unshaded ones are different. The first one says:
If there's a name in column C and there's nothing in column O (headed 'Reason') then highlight column M (headed 'Actual Thru Date (If eval is not annual)')
Maybe that one should be deleted and the formatting left to the other one in that cell: =AND($C3<>"",$R3<364) which highlights (slightly differently) column M if there's something in the Name column and column R ('Rated Days') contains a value less than 364. You tell me.

The second one says:
If there's a name in column C and there's nothing in column Z (headed 'Senior Rater Effective Date') then highlight column AA (headed 'Supplementary Reviewer Required')
Is that as intended?

[I've been looking at just row 3's conditional formatting where I see more than 100 conditional formats (for just 42 cells!). I think I can get this down to less than 20, perhaps even less than 10, I can then apply these to the whole table, meaning there'll just be a handful of conditional formats for the entire sheet. This will be manageable, resource-light, and if someone should mess up the formatting a small macro could re-instate the correct formatting in a jiffy.]

 

[rbercher]

Those are mistakes then and can be removed completely. A lot of the formulas are for error checking to make sure that things are either requirements that need to be filled out or standard-based error checking based on regulations.

Columns W and X are to make sure that the right Senior Rater is aligned with the correct rank of the person being evaluated based on the table in the data tab. Though, I'm sure you can see that. It's the only way I could figure out how to make that work, so if you've got a better way, feel free to change that as well. I attached the table (if that helps) I tried to dynamically reference the dropdowns in the Senior Rater Rank column to ensure the correct Senior Rater ranks in column S were used based on the input in column B to prevent mistakes.

 

[p45cal]

Currently, for column K you have umpteen CFs (conditional formats) which could be reduced to 3. That's easy. But I'd like it down to 1 for the entire column.
Currently it's red/yellow/green according to today's date distance from the date in that column, thresholds being 1 and 3 months.
To get it down to one we could use a 3 colour scale where you see this sort of thing:


This is a screen shot taken 12 Apr 2021 (so there's one overdue date).
1. Would this be OK to use?
2. Will the version(s) of Excel this workbook will work in support it (see below to see if it exists on your system)?

 

[rbercher]

Yes, it will and that will work perfectly. I actually think that may work better than the way I had it where it's strictly three. The only thing I don't like the brighter yellow. I would prefer it be more of an amber color, but I can mess with all of the picky things. You've done so much already, I don't want to overburden you nor take advantage of your kindness and willingness to help me out.

 

[p45cal]

OK Will do. Now to column AL. This has 4 CFs but still with 3 thresholds -71,0,90. Again can get it down to 1 CF for the whole column BUT it doesn't do the font changes (bold/white etc.), only the fill. Below your CFs (with the softer yellow you used) in the column marked original, and a 3-scale approximation CF in the other one.
Approve?

 

[rbercher]

Yeah, that looks fine. The white text was in case we use in a PowerPoint; though I don't see that happening and I can always change the color for readability if that is the case.

 

[p45cal]

I've got to doing the CFs for columns N, O, Q, R, AH:AJ.
All of these look at column O (Reason).
Since the existing CFs (thousands of them) are inconsistent, even within a column, I'd like some general and specific guidance (in words, not formulae) on how you want them.
For example, there seems to be an exception for 'Annual' sometimes. Sometimes the CF formulae for the same formatting include 6 items:
Senior Rater Option, Change of Duty, Complete the Record, Annual, Change of Rater, Extended Annual
sometimes 5:
Senior Rater Option, Complete the Record, Annual,Change of Rater, Extended Annual
others 4:
Senior Rater Option, Complete the Record, Change of Rater, Extended Annual

 

[rbercher]

Not a problem:

There are three date thresholds based on local policy for annual evals:

Column N should add 364 days if column O is Annual.
305 days or less days (green), no action;
305 to 364 days (amber) the drafts should be getting done and reviewed with the eval being completed within that time frame;
365 or more (red) and the eval is late and required immediate action

Otherwise, it should take the date that is entered into column M and check the same thresholds.

Column O is error-checking to make sure that if column M has something in it then column O should not have Annual in it. If it does, it needs to highlight as an error to change. Otherwise, it is just checking to make sure that if there is data in columns C that it is filled in.

Column Q is error-checking to see if column O is annual or not. If it is annual, there should be 0 non-rated days. If there are, then it needs to highlight to inform the user that they have the wrong reason in column O and need to change the type of eval it is.

Column R is similar except that it is checking to see if the rated days are greater than 364. If so, then it should be highlighted because all evals should not have more than 364 days of rated time.

Annual and Extended Annual evals are allowed to have 364-365 days.

If it has less than 364 days then it needs to check if the eval is either "Annual" or "Extended Annual". If it is then it needs to highlight. Any other eval type is allowed less than 364 days but have more than 363 days.

Columns AH:AJ are similar to column N:
AH highlights based on when a draft is due to the company which is 60 days from the date in AK:
60 or more days (green), it's early to the company
59 to 45 days (amber), draft should be build and submitted for company review
44 or less days (red), it is late to the company

AI and AJ are the same except to the BN or BDE depending on who the senior rater is:
46 or more days (green), it's early to the BN
45 to 30 days (amber), draft should be to the BN for review
29 or less days (red), it's late to the BN

I hope that helps to clarify those columns. If you have other questions or need further clarification, let me know.

 

[p45cal]

Column N:
In a single row you have 19 different CFs for column N:

I note there are 6 distinct values in column O catered for; so:
1. can column O contain values apart from these 6 possibilities?
2. if so, confirm that you want no colouring at all if O isn't one of these 6

I can certainly reduce the yellow and green CFs to one CF per colour, but I'm not sure of the red CF, in the orange box in the picture, there could be some anomalies; 3. are these typos or have they been carefully thought out?

None of these conditional format formulae look at column N at all! 4. So might it be an idea for column N's sheet formula (not the CF formula) to produce a date taking into consideration column L, M & O and then get the CF formula to look at only column N?
You imply as much here:

Column N should add 364 days if column O is Annual.

but I can't see such a formula! I have only =IFERROR(IF($L3="","",IF($M3="",SUM($L3+364),$M3)),""), 5. can you say what formula it's supposed to be in N3?
6. I might also be able to reduce all these 18 CFs to just 1 using a 3-colour scale again?

 

[rbercher]

For column O, I need it to highlight black with red text if it doesn't say annual and column M is not filled out with a date and red if it is empty but there is data in columns C and L. I also need it to highlight black with red text if it is annual and column M is filled. This is to error check that the user has the correct eval reason filled out. Otherwise, it should have no color or be white since it is correct data.

Is there a way to give a different drop-down menu based on if column L and M are filled or if just column L is filled out? The basic idea of this error checking is to ensure that if column O says anything other than Annual, that column M is filled out and the other options (not annual) are selected. If that was possible, I attached a spreadsheet that has all of the eval codes and put the types of evals from our policy under them in a table format. The top one is for officers who get what's called an OER and the bottom one is for NCOs that get what's called an NCOER. You can see in the data tab what the NCO ranks are and the rest are officers. If we could dynamically create drop-downs based on the rank of the individual being rated (evaluated), that would save A LOT of headache in what kind of evaluations one has to choose from.

I'm not really sure why all of those are in Column N, to be honest. They may have been accidental transfers from copy/pasting operations that I did and I just didn't delete them. Those can be removed because Column N has no bearing on any error checking or required fields for coloring. I do, however, like the idea of it producing the date based on the idea you're mentioning here:

None of these conditional format formulae look at column N at all! 4. So might it be an idea for column N's sheet formula (not the CF formula) to produce a date taking into consideration column L, M & O and then get the CF formula to look at only column N?

It would probably be more efficient to have it look at all three columns, produce the date, then CF off of the date inside as it gets closer to change from green->amber->red.

My thoughts behind the CF formulae was to have it looking at column L and M (depending on which one is filled out) and checks the date produced against today for CFs. As the date in column N gets closer, the color should go from green->amber->red. So, if I only have column L filled out, it should be calculating that date to add 364 days to it. Otherwise, it should just take the date entered into column M and, as that date gets closer, change from green->amber->red. I hope that makes sense. I think I mis-spoke from what I said before. Sorry for the confusion on that.

 

[imegut0421]

Hi, I got the same (If condition not met then go to the next row) problem too but not that complicated. I want to summarized all the "Apples" in consecutive rows. I attached sample data where cell A3= Apples, A4=Apples, A5=Banana, A6= Banana, A7= Banana and A8=Apples. Using the if then formula on column D, it will go like this:

D3= Apples
D4= Apples
D5= 0
D6= 0
D7= 0
D8 = Apples

But I want my report to be:

D3= Apples
D4= Apples
D5= Apples (being the D5 to D7 is zero, so it should jump to the next row with Apples).

Please help. Thanks.

 

[Luke M]

Hi, I got the same (If condition not met then go to the next row) problem too but not that complicated. I want to summarized all the "Apples" in consecutive rows. I attached sample data where cell A3= Apples, A4=Apples, A5=Banana, A6= Banana, A7= Banana and A8=Apples. Using the if then formula on column D, it will go like this:

D3= Apples
D4= Apples
D5= 0
D6= 0
D7= 0
D8 = Apples

But I want my report to be:

D3= Apples
D4= Apples
D5= Apples (being the D5 to D7 is zero, so it should jump to the next row with Apples).

Please help. Thanks.

Although a similar topic, you should start your own thread rather than jumping onto an existing one. A thread with 0 replies is also more likely to get a response from a member of our community.

 

[p45cal]

@rbercher , nearly 2 weeks ago I started a private conversation with you here at Chandoo explaining the reasons for my recent absence from these forums, where I asked the question, so far without response:

could you (1) confirm you're still looking for a solution and (2) send me a file if you think that last one we were working on is somehow out of date?

Perhaps you've just moved on…