+ Reply to Thread
Results 1 to 4 of 4

Help with Charting different dates for Vendors

  1. #1
    Registered User
    Join Date
    02-13-2020
    Location
    San Francisco, CA
    MS-Off Ver
    2016
    Posts
    2

    Help with Charting different dates for Vendors

    Hi, I have a slight problem.

    I'm trying to make a bar chart with our vendors to see who worked together during the same period. My problem is that some vendors were working intermittently during the year.

    Is here a way for me to make a bar chart for this?

    I attached a snip and a sample workbook.

    Thank you!

    Capture.PNG
    Attached Files Attached Files

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,349

    Re: Help with Charting different dates for Vendors

    Your workbook has the same content as the screenshot, i.e. a few colored cells. I assume that this is the desired output. You don't say.

    In order to generate that output, please provide the source data and explain the logic to arrive at the result.

  3. #3
    Registered User
    Join Date
    02-13-2020
    Location
    San Francisco, CA
    MS-Off Ver
    2016
    Posts
    2

    Re: Help with Charting different dates for Vendors

    Hi, Teylyn

    Sorry about that. Here's the file,

    So basically what's happening is we have several vendors/subcontractors who invoiced us during different periods and I want to make a horizontal bar chart (similar to a gantt chart), so we can see which vendors worked together during each quarter.

    Thank you!
    Attached Images Attached Images
    Attached Files Attached Files

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,349

    Re: Help with Charting different dates for Vendors

    get rid of the merged cells in your data table and add the correct vendor name to each row.

    In the result grid, make sure that the cells with the month name contain real dates, the first of each month. You can format the cells to show just the month name.

    Now you can fill the table with formulas that count the number of invoices for that vendor in that month. For example

    =COUNTIFS($C:$C,">="&G$4,$C:$C,"<"&EDATE(G$4,1),$B:$B,$F5)

    This formula counts the cells where column C is greater than the date in G4, column C is smaller than the date in G4 plus one month and column B is equal to the value of F5. See the attached file.


    As a final step you can use conditional formatting to format zero results with white text on white and number results with blue text on blue.
    Use a conditional formatting rule with a formula. The formula for white cells is

    =G5=0

    The formula for blue cells is

    =G5>0

    The format is applied to the body of the table. G5 was selected when the format was created. Note that there are no $ signs in the formula.
    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)

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