Highline Excel Class 09: SUMIF Function Formula 3 Examples

Hi, I need help in writing functions maybe for example to find if named sheet exist with x name...etc if not do this else that...or
to see if col has dates...
My actual question would be to learn how to write this kind of function.

Thanks in advance.
Pedie

Hi, i need help on how to combined the sumif function and match/vlookup function - i need to match the code infront. Can you help me as example below please?

Below is the raw data

CODE REV 870002 Occupancy Movements 0.4 870003 General Works Move 0.55 870005 Non IntraGIE 4.5 870006 O/U Movement 2.2 870002 Occupancy Movements

6.4 870006 O/U Movement 3.3 870002 Occupancy Movements

5.3


Final output :

code rev 870002 870006 870005

Hello All,
I have a small but annoying issue. I have a spreadsheet with two columns in it. The A column has finished part numbers in it and the B column has the exact same finished part numbers but I need to remove anywhere from 1 to 4 zero's that are at the front of the finished part numbers.
Is there a way I can use the TRIM function to go through the entire column instead of one cell at a time. I have 5789 lines to go through.
Any assistance would be greatly appreciated. Thank you

i have a task of producing a spreadsheet that asks questions and takes figures then displays them as a report.

e.g how much is your energy bill? the user types a figure, its calculated in a formula, and the results displayed in a report.

I need it set out so it looks professional and user friendly. Has anyone got any examples of well laid out user friendly colourful spreadsheets?

Would appriciate it if some one could help me.

Thanks a great deal

Hi,
I am looking for a good website specially a video tutorial that shows you how to create dashboards or charts. I am looking for similar website like http://www.datapigtechnologies.com/
this is very good website for those of you who need to do some fancy staff with excel. Any help will be appriciated. Thanks.

Hello everybody,

Can anybody upload the
2500 Excel VBA Examples, if you have.

hi, i would like to know the site or resource for mastering Loops like DO loops and other loops with examples.if any body knows some sites do list here.

I have the following SUMIF formula (array) that searches through the range I30:I70 and sums any cells that meet the criteria in H80. The value in H80 is 18:00 formatted as time.

I need to also have the function sum any cells that have the value DLR-18 in them also. Obviosly DLR-18 is not formatted as time.

Thanks for any help.

{=SUM(IF(I30:I70=H80,1,0))}

I'm trying to use a class for defining a set of data and also to get values from two different ways, a database and a spreadsheet. Let me explain with an example.

I created a class called clsData with the following code:

Public conn As ADODB.Connection
Private p_isin As String
Private p_tick As String

Public Property Let isin(myvalue As String)
p_isin = myvalue
End Property
Public Property Get isin() As String
isin = p_isin
End Property
Public Property Let tick(myvalue As String)
p_tick = myvalue
End Property
Public Property Get tick() As String
tick = p_tick
End Property


I inserted a public function inside that class, in order to retrieve the data in the two mentioned ways:

Public Function GetData(myvalue As String, DB As Boolean) As clsData
Dim rs As ADODB.Recordset
Dim sql As String
Dim c As Range
Dim myData As clsData
Set myData = New clsData

If DB Then
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseServer
sql = "SELECT * FROM dbData WHERE isin='" & myvalue & "'"
rs.Open sql, conn

If Not rs.EOF Then
myData.isin = rs("isin")
myData.tick = rs("tick")
End If
rs.Close
Else
With ActiveSheet
Set c = .Range("A:A").Find(myvalue, LookAt:=xlWhole)
If Not c Is Nothing Then
myData.isin = .Cells(c.Row, 1)
myData.tick = .Cells(c.Row, 2)
End If
End With
End If
GetData = myData
End Function

All that code goes into clsData. Now I add a standard mode with this code:

Public Function GetClassData() As clsData
Set GetClassData = New clsData
End Function

Sub myTest()
Dim AllData As clsData
Set AllData = New clsData
AllData = GetClassData.GetData("FI0009000681", False)
End Sub

The first function is in order to access the class and the function inside
The routine myTest is trying to retrieve the data I need

I get an error in last line of the function: "GetData = myData". Error message window says "Object variable or block With not established" (actually it is a translation of my current Excel version)

Anybody knows what is wrong with this code?
I can send the whole code to anybody who can help

JC