Finding Formula

[R J Solanki]

Dear All

I follwoing data in sheet 1

A B C
1 Sr No. Budget Expended
2 100 5000 (***)

& I also have sheet 2 as follow

A B
Sr No. Amount
1 100 250
2 101 500
3 100 750
4 100 350

Now what i want in column C2 (***) of sheet 1 that whatever of amount (Col.B) against Sr. No. 100 in sheet 2 is added and total will appear in C2 of Sheet 1 & When C2 exceed B2 amount (i.e. sheet 1) i.e. 5000 then a dialogue box appear in sheet 2 that buget exceed

Thanks in advance

 

[Comfy]

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Sr No.</td><td style=";">Budget</td><td style=";">Expended</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">100</td><td style="text-align: right;;">5000</td><td style="text-align: right;;">3000</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">101</td><td style="text-align: right;;">2000</td><td style=";">Budget Exceeded</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">SUMIF(<font color="Red">Sheet2!$A:$A,Sheet1!$A2,Sheet2!$B:$B</font>)>$B2,"Budget Exceeded",SUMIF(<font color="Red">Sheet2!$A:$A,Sheet1!$A2,Sheet2!$B:$B</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=IF(<font color="Blue">SUMIF(<font color="Red">Sheet2!$A:$A,Sheet1!$A3,Sheet2!$B:$B</font>)>$B3,"Budget Exceeded",SUMIF(<font color="Red">Sheet2!$A:$A,Sheet1!$A3,Sheet2!$B:$B</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Sr No.</td><td style=";">Amount</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">100</td><td style="text-align: right;;">1000</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">101</td><td style="text-align: right;;">1000</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">100</td><td style="text-align: right;;">2000</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">101</td><td style="text-align: right;;">2000</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

 

[Comfy]

re-read your last part will post some VB for your message box on sheet 2

 

[R J Solanki]

Thanks for quick reply i will check it out

 

[Comfy]

Put this in a module for Sheet2

Private Sub Worksheet_Change(ByVal Target As Range)
Dim SN As String, match As Range
    If Intersect(Target, Range("B:B")) Then
    SN = Target.Offset(0, -1).Value
        With Sheet1
            Set match = .Columns(1).Find(What:=SN, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole)
            If Not match Is Nothing Then
                If match.Offset(0, 2).Value > match.Offset(0, 1).Value Then MsgBox "Budget Exceeded for Sr No. " & SN
            End If
        End With
    Else
    Exit Sub
    End If
End Sub

 

[R J Solanki]

Thanks a lot it works
But i want to know why "$" sign is used in formulla specially the cell highlighted below i.e. $A2
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=64>IF(SUMIF(Sheet2!$A:$A,Sheet1!$A2,Sheet2!$B:$B)>$B2,"Budget Exceeded",SUMIF(Sheet2!$A:$A,Sheet1!$A2,Sheet2!$B:$B))

</TD></TR></TBODY></TABLE>

 

[Comfy]

Force of habit really.

It means that if you were to copy that cell to another it would effect the destination cell differently.

E.G.

The formula was in sheet1 B2

If it was copied to C2 the reference would remain as A2
If it was copied to B3 the reference would become A3.

It essentially locks the column or row.

See: http://www.cpearson.com/excel/relative.aspx