Excel data bar or graph formula issue

[cchurchill]

Greetings all, I appreciate the fact you are taking time to read this in the first place.
(The attached files have fake names and data and used only to show my objective)

Objective: Record monthly pledges per person with a running total. Example: 30 people donating 120/mo for two years. Showing with a bar a percentage of what came in that month... if they were short on the pledged amount or anything greater would still = 100%

Option#1: when doing data bars - I am trying to fill each bar based on a % of what they gave based on their pledge but it doesn't code correctly. If CC pledged 120 but only gave 60 for January = 50% of the bar colored. The biggest problem is if I have to code each person to display the correct percentage based on pledges being different amounts.

Option#2 - Would it be easier to just do a graph of the months on one axis and the names on the other with the pledge data showing on each month? I have tried this but the month data does not display correctly. Ill post a picture to show you what I'm running into.
I want the names on on axis, and the months of two years on the other axis... with each bar equaling the donated amount shown per month.

The months do not display correctly.

Thank you for your time in this, as I am mentally exhausted and I think that's half the reason I can't complete this simple project. lol. Cheers.

 

[crouchsw]

Have a column that holds the pledge amount. Edit the conditional formatting for your Data bars; change the max type to formula, and set the equation to the cell that contains the donation amount. You'll have to do it for each row, since the data bars don't like relative cell addresses.

 

[cchurchill]

You got it. I was eluding to this when I mentioned the doing coding for each one and that's part of it - as your formula is spot on. Thanks for your help... now to add 300 rules lol.

 

[crouchsw]

VBA to the rescue - hopefully you haven't been working on it too long:

Sub copydatabars()
Dim target As String
For i = 2 To 9 'first row w/o data bars to last row with data
target = "=Sheet1!B" & i
ActiveSheet.Range(Cells(i, 3), Cells(i, 26)).Select 'change the 26 if you have more than 24 columns with data

    With Selection
    Selection.FormatConditions.AddDatabar
    Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
        .MaxPoint.Modify newtype:=xlConditionValueFormula, newvalue:="=" & Cells(i, 2).Address
    End With
    With Selection.FormatConditions(1).BarColor
        .Color = 13012579
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
    Selection.FormatConditions(1).Direction = xlContext
    Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
    Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
    Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
    With Selection.FormatConditions(1).AxisColor
        .Color = 0
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).NegativeBarFormat.Color
        .Color = 255
        .TintAndShade = 0
    End With
    End With
Next
End Sub

 

[cchurchill]

Let me ask you this last question or problem I'm having -- heres a photo...

The bottom does not pull the dates correctly and when I edit them it deletes them instead of fixing it. How do I change the bottom to display the months correctly?

 

[cchurchill]

it originally looked like this:

 

[SirJB7]

Hi, cchurchill!
If a photo is worth a thousand words, here at these forums a workbook is worth a thousand photos.
Regards!

 

[crouchsw]

Looks like you've formatted the month numbers as a date (when you see a date, it is stored in excel as the number of days since 12/31/1899, so 1 = 1/1/1900, 2 = 1/2/1900, etc) - you can either set it back to general, or change the values to the first of the respective month (which would probably be the better option). So, change the 1 to 1/1/2013, 2 to 2/1/2013, etc.