if column is blank then display "-" ; opening file thru a fname

[jv]

Good day to all,

1)I have this summary of overtime of personnel per month
distributed in columns and the last column is the total.

column: A B C D E
( Name ) ( Aug ) ( Sept) ( Oct ) ( Total )
1 - Davantes 45 30 12 87
2 - Smith 75 20 - 95
3 - Ford - - - 0

I need to appear in the total column a symbol of "-" if
there are no entry in a the whole months.

2) I have this list of Equipments in one file
corresponding to a specific equipment in the other file.


Filename: equipmentlist.xls
( this file contains the ff. )

1 - Electrolux.xls
2 - Melitta.xls
3 - Cimballi.xls
4 - Black&Decker.xls
5 - National.xls
6 - Sanyo.xls
7 - Sharp.xls

Question : is it possible just by pressing on any of this
file or any of each item no. will automatically open the
specific file?

For your kind assistance.

Thank you in advance.

jv

 

[Paul]

1)I have this summary of overtime of personnel per month
distributed in columns and the last column is the total.

column: A B C D E
( Name ) ( Aug ) ( Sept) ( Oct ) ( Total )
1 - Davantes 45 30 12 87
2 - Smith 75 20 - 95
3 - Ford - - - 0

I need to appear in the total column a symbol of "-" if
there are no entry in a the whole months.

Try this array formula in E1:
=IF(AND(ISBLANK(B11)),"-",SUM(B11))
An array formula has to be entered using CTRL+SHIFT+ENTER rather than just
ENTER.

 

[jv]

Paul,

Thank you for helping me out, do you have any comment on
2nd question?

jv

 

[Dave Peterson]

#2. It would scare me to just open the file when I selected the cell. And
excel doesn't have a single click event that you can tie into. But you could
double click on the cell and get it to open.

Right click on the worksheet tab that has this list. Then select view code.
Paste this in:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

Dim myWkbkName As String
Const myFolder As String = "C:\my documents\excel\"

If IsEmpty(Target) Then Exit Sub

myWkbkName = Target.Value
If LCase(Right(myWkbkName, 4)) <> ".xls" Then
myWkbkName = myWkbkName & ".xls"
End If

If Dir(myFolder & myWkbkName) = "" Then
Beep
Else
Cancel = True
Workbooks.Open Filename:=myFolder & myWkbkName
End If

End Sub

Change the folder name to match your files.

 

[Guest]

Dave,

The program is too powerful it helps alot.

Is there any website that teaches the way you shared to me.

Thanks and regards.


jv

 

[Dave Peterson]

There are lots of sites that deal with excel.

John Walkenbach has a list of links at:
http://j-walk.com/ss/excel/links/index.htm

But I really like the newsgroups. You can learn a lot by lurking, then posting,
then getting corrected <bg>.