PDA

View Full Version : Solved: Formula help



austenr
10-24-2006, 06:38 AM
I need to calculate a total. for instance, a1*b1 then take the answer and add it to what was in b1 to begin with. The only way i can think of is a two step process. Thanks

mvidas
10-24-2006, 06:59 AM
Austen,

So you want B1 to be B1+(A1*B1) ? So if A1 contains 2 and B1 contains 4, you want B1 to then contain 12?

Unfortunately Paste Special doesn't allow you to multiply and add, so you would have to use a third column, then paste special the values back into B:
=B1+A1*B1

Matt

austenr
10-24-2006, 07:09 AM
Thanks buddy. I was hping not to have to do it that way. BTW, on an unrelated subject what do you have to do to re-install te MSDN collection?

mvidas
10-24-2006, 07:14 AM
Paste special would have allowed you to Add something to B, or multiply A by B, but unfortunately for both operations you'd have to use the formula.

Not exactly sure about the MSDN thing, I would assume it would be on the VB CD. It isn't anything you can download that I'm aware of

austenr
10-24-2006, 07:43 AM
ok thanks

johnske
10-24-2006, 02:15 PM
Sub DoCalculation()
[B1] = [B1+(A1*B1)]
End Sub

austenr
10-24-2006, 03:24 PM
Thanks John

johnske
10-24-2006, 03:39 PM
It's easier with VBA (you avoid the circular reference problem). You could also make it a change event E.G.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.EnableEvents = False
[B1] = [B1+(A1*B1)]
Application.EnableEvents = True
End If
End Sub

johnske
10-24-2006, 03:47 PM
Or, to apply it to the entire column, Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Application.EnableEvents = False
Target = Evaluate(Target + (Range("A" & Target.Row) * Target))
Application.EnableEvents = True
End If
End Sub

Zack Barresse
10-24-2006, 04:00 PM
What exactly do you want installed with the MSDN Collection? Do you have the DVDs or web access to the site?

austenr
10-24-2006, 04:05 PM
Hi Zack. Im not really sure. My predicesor was a, well, I cant say that on here. Anyway he re engineered a bunch of Access code into SQL Server. Now the powers that be and me want something that is not so tied to SQL, stored procedures and some other junk. It is that reference (VB T-SQL) that the save function is looking for. Cant find it and as far as I know is not downloadable from MSN.