+ Reply to Thread
Results 1 to 7 of 7

Banded row format on table, but banding depends on cell value?

  1. #1
    Registered User
    Join Date
    04-25-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    72

    Banded row format on table, but banding depends on cell value?

    I am hoping to make one of my reports easier to read. Right now, it has one of the default banded row formats (every other row).

    However, I would like to band the rows based on whether they shared the same value (order number) with the rows above and below them.

    Is this possible?

    Table 1 in sample is the current format.
    Table 2 in sample is the goal.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Banded row format on table, but banding depends on cell value?

    How do you decide on the bandings? Odd numbered orders? Even numbered orders? Single line orders?
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    04-25-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    72

    Re: Banded row format on table, but banding depends on cell value?

    Quote Originally Posted by TMS View Post
    How do you decide on the bandings? Odd numbered orders? Even numbered orders? Single line orders?
    I wanted the bandings to group orders with the same order number. So if I had 10 lines with order# 2343678 they would all be one band, then the next row of order# 56755767 would be a different band.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Banded row format on table, but banding depends on cell value?

    As TMS suggests, the highlighting in the second table could be obtained using conditional formatting so that the odd numbered orders are highlighted. Since the order numbers are sequential, that will work out so that orders are banded by group.
    Select A13:D19
    CF formula: =ISODD($B13)
    Choose fill color.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    04-25-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    72

    Re: Banded row format on table, but banding depends on cell value?

    Hi JeteMc. I had a similar thought but my issue was they banded together multiple orders when I had a few in a succession with odd numbers. What I am looking for might not be possible.

    Appreciate the help anyways.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Banded row format on table, but banding depends on cell value?

    The following proposal employs a helper column (F) which may be moved and/or hidden for aesthetic purposes.
    The first row of the helper column is populated using: =TRUE
    The rest of the helper column is populated using: =IF(B14=B13,F13,NOT(F13))
    The conditional formatting formula is: =$F13=TRUE
    If this doesn't help please give us an example that is as close to the actual set up as possible.
    Let us know if you have any questions.
    Attached Files Attached Files

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Banded row format on table, but banding depends on cell value?

    You can do it by adding a Helper column BUT not connected to the existing Table and NOT using Structured Table entries.

    See the attached updated example.
    Attached Files Attached Files

+ 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. Formula help - Banded table
    By Dooberry in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-25-2017, 10:59 AM
  2. Add banded rows to custom pivot table style
    By stephme55 in forum Excel General
    Replies: 0
    Last Post: 11-10-2016, 07:39 PM
  3. Replies: 2
    Last Post: 07-21-2015, 03:48 AM
  4. Possible VBA for adjusting fixed table banded rows with pivot table banded rows
    By Gunner182 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-14-2015, 03:44 PM
  5. How to format rows when its formatting depends on the value of other cell
    By Tutai in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-13-2014, 06:15 PM
  6. Ms access Vba Save Data to xl depends upon 1 table pick & Depends 2 Upon fiterDate
    By breadwinner in forum Access Programming / VBA / Macros
    Replies: 14
    Last Post: 05-29-2014, 10:59 PM
  7. Excel 2007 : Banded rows in table
    By Bernt in forum Excel General
    Replies: 1
    Last Post: 04-13-2011, 05:38 AM

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