# Finding Formula

#### R J Solanki

##### New Member
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

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

<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 />

Thanks for quick reply i will check it out

Put this in a module for Sheet2

Code:
``````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``````

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>

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

Replies
2
Views
269
Replies
0
Views
199
Replies
4
Views
422
Replies
4
Views
679
Replies
3
Views
252

1,203,052
Messages
6,053,223
Members
444,648
Latest member
sinkuan85

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back