+ Reply to Thread
Results 1 to 18 of 18

How do I cause excel to skip number sequence using colour?

  1. #1
    Registered User
    Join Date
    12-06-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    22

    Question How do I cause excel to skip number sequence using colour?

    I have an example I would like to make a reality with a formula for Column C. Please check the image attached.

    SS.jpg

    In Column B, I have used a formula to create a naming convention using Column A (check the formula bar in the picture above for reference).

    Column C is what my desired result should be at the end. I have just typed the values without formula. But this is how I want them to be after formula.

    Essentially I want Column C to skip the lines with red/yellow fill (in Column A & B) and continue the sequence only when cell A and B fill is blank or not yellow/red.

    The cells are NOT conditionally formatted. Instead I'm using a VBA function to identify the red and yellow cells:
    Please Login or Register  to view this content.
    How do I write an IF statement to cause Column C to skip the lines using the red and yellow colored fill in Column A and B? The number sequencing needs to continue as mentioned above. So if ABC123_0001 to ABC123_0003 is blank or coloured NOT yellow or red, then no problem. But if ABC123_0004 is red or yellow, then it must skip this line in Column C. Then if the next cell is not colored red/yellow then it will continue as the new ABC123_0004 and so on. Hope that makes sense.. Refer to the picture for how exactly I want it.
    Last edited by Sampoline; 12-07-2020 at 01:02 AM. Reason: Solved issue.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,538

    Re: How do I cause excel to skip number sequence using colour?

    Without condition for yellow/red cells, without VBA, I belive it can not be done with formula.

  3. #3
    Registered User
    Join Date
    12-06-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    22

    Re: How do I cause excel to skip number sequence using colour?

    I don't think you are reading my full post correctly. I've added my VBA function in the OP. Please review again above. All I'm asking for is the IF statement I can use to do what I've done in Column C.

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,538

    Re: How do I cause excel to skip number sequence using colour?

    Yes, I did read it. Formula in column C can not read format of column A or B .

  5. #5
    Registered User
    Join Date
    12-06-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    22

    Re: How do I cause excel to skip number sequence using colour?

    Ok say that I add a helper column like below:

    Now in column C, I have the following formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    As you can see in the below picture, column C shows value of "Y" if colour is yellow or red.

    Attachment 707465

    Then in Column D, I have the formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Which skips when Y is found in Column C. But still not changing sequence number "_0000" as I wanted. Any solutions for this or better methods of tackling this, without a helper column would be beneficial..

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,538

    Re: How do I cause excel to skip number sequence using colour?

    Attachment is invalid. Try to attach worksheet instead.

  7. #7
    Registered User
    Join Date
    12-06-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    22

    Post Re: How do I cause excel to skip number sequence using colour?

    Hi does the attachment work this time?
    Attached Files Attached Files

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,538

    Re: How do I cause excel to skip number sequence using colour?

    Column C does not work because code is missing. Try to add the code and upload again.

  9. #9
    Registered User
    Join Date
    12-06-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    22

    Re: How do I cause excel to skip number sequence using colour?

    Sorry, forgot to save as a macro-enabled worksheet. Hopefully this works.
    Attached Files Attached Files

  10. #10
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: How do I cause excel to skip number sequence using colour?

    crossposted: https://www.mrexcel.com/board/thread...olour.1155004/

    Rule 03: Cross-posting Without Telling Us

    Your post does not comply with Rule 3 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

    Post a link to any other forums where you have asked the same question.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this.

    I have added the crosspost reference for you today. Please comply with this and all our rules in the future
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  11. #11
    Registered User
    Join Date
    12-06-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    22

    Re: How do I cause excel to skip number sequence using colour?

    Ok didn't know about this. Won't do it in the future, but my issue wasn't being resolved till now. If it did, I would have selected solved on this forum. Thanks.

  12. #12
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: How do I cause excel to skip number sequence using colour?

    Did you read the link to understand why we ask that you post crosspostings.

  13. #13
    Registered User
    Join Date
    12-06-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    22

    Re: How do I cause excel to skip number sequence using colour?

    Yes I have read it now. Informative. Haven't really used excel forums too much before tbh. Only asked my first question last week. I'll learn. Thanks. Still struggling to get some answers in either forums, in that case what would you suggest I do?

  14. #14
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,888

    Re: How do I cause excel to skip number sequence using colour?

    Stay the course as you have posted in both and appears to be getting help in both and crosspostings are known.

  15. #15
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,538

    Re: How do I cause excel to skip number sequence using colour?

    Try in E2:

    Please Login or Register  to view this content.
    Drag down

  16. #16
    Registered User
    Join Date
    12-06-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    22

    Re: How do I cause excel to skip number sequence using colour?

    Yeah I didn't realise that cross-posting was actually a thing in forums. I'm not experienced at this. Didn't cross my mind that volunteers work to give solutions across multiple forums and may see my question twice. Or someone else may answer, leaving another volunteer frustrated. In hindsight, that does make a lot of sense. Thanks for the information. And my apologies for cross-posting. Hope my question will be solved soon.

  17. #17
    Registered User
    Join Date
    12-06-2020
    Location
    Sydney, Australia
    MS-Off Ver
    2013
    Posts
    22

    Re: How do I cause excel to skip number sequence using colour?

    Quote Originally Posted by bebo021999 View Post
    Try in E2:

    Please Login or Register  to view this content.
    Drag down
    Thankyou bebo, this solved my issue.

  18. #18
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,292

    Re: How do I cause excel to skip number sequence using colour?

    Quote Originally Posted by Sampoline View Post
    Yeah I didn't realise that cross-posting was actually a thing in forums. I'm not experienced at this.
    As a new member, please take the time to review our rules. There aren't many, and they are all important. Cross-posting is just one of them.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] In a range of cells, sum the numbers in sequence and skip the text values
    By redJohn89 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-02-2019, 11:33 PM
  2. Skip number in sequence
    By ldoodle in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-18-2016, 08:52 AM
  3. Excel Next Number in Alphanumeric Sequence
    By danmachen1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-24-2015, 02:46 PM
  4. Replies: 7
    Last Post: 06-08-2015, 08:39 AM
  5. How do you fill a series of cell references and skip four to produce a sequence
    By john dalton in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-02-2014, 07:25 AM
  6. [SOLVED] Function to skip the process if the sequence number is missing
    By Thinker8 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-15-2014, 08:20 AM
  7. Excel Charts Colour Fills and Sequence of arrangement
    By superkid in forum Excel General
    Replies: 2
    Last Post: 02-22-2009, 09:33 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1