Isblank question

[Mitja Bonca]

I am doing a program in excel for presence at work.
I did a year`s calendar in which I want to enter hours for each day (presence, vacations, when sick,..). And when I want to sum up hours from a whole month, I want to have nothing written in the total item. Just afer entering one value in one particular day, then a value in a Total enters as well (otherwise there are Zeros- but I dont want to have zeros, just an empty cell)

Here is the equation of my example:
F130=IF(ISBLANK(F4);T(0);F4+F8+F12+...)

F130 is a TOTAL of the month,
F4 is the cell of the 1st day in January, F8 is 2nd day in January, ...)
If I enter the number x into F4 it will work like I want to (into cell F130 will be written x), but if I start entering into F8, in F130 will be written NOTHING.

What do I have to do?
ISBLANK only work with one cell. How do I convince excel that it has to enter into a number into F130 whenever I enter a number into F4,F8,F12,F16,...?

PLEASE for a help!

[martindwilson]

=if(f4+f8+f12+d12.....=0;"";f4+f8+f12+d12.......) perhaps

[Mitja Bonca]

Thank you very much!! It works
thank you.

[Mitja Bonca]

Do you know maybe if is it possible to programmate in excel maybe?
An example what I would like:
- In A1 cell you have written "Enter the name here"
- When you click on it you then enter your name
- and then that cell locks or something - it would be good (but i know this is not C# or something )
- then this name automatically appears in some other cell (A2 for eaxmple)
and A2 can not be changed or rewrite- much be lock for to do anytihng
-but before you enter the name A2 has nothing written there


I know I am complicating, but if is it possible to do sometihng like it?

[ChemistB]

Hi Mitja,
You should start a new thread when starting a new topic.

Excel uses VBA (Visual Basic for Applications) and it can do what you want it to do. Is this sheet protected to begin with or is it unprotected?

[Mitja Bonca]

Hi Mitja,
You should start a new thread when starting a new topic.

Excel uses VBA (Visual Basic for Applications) and it can do what you want it to do. Is this sheet protected to begin with or is it unprotected?

No, its not protected.

[Mitja Bonca]

double post

[ChemistB]

If you want to keep certain cells from being written into or formula's from being compromised, it's always a good idea to protect the worksheet (Tools>Protection>Protect sheet).
Before protecting the sheet, determine which cells you want the user to enter data into and format those cells to be unlocked (you can select all the cells simultaneously and do this). In your example, A1 would be unlocked and A2 would be locked. Then you want a macro that unprotects your sheet, moves your value in A1 to A2, then locks A1 (A2 was already locked) and reprotects the worksheet. Yes?

[Mitja Bonca]

For to use Macros? I don`t have a clue. Is there needed to do any codeing?
Can you please show/writen how would be this word transfering from A1 to A2!?
The word that I enter into A1, has stay from then on in A1 as well.

[Mitja Bonca]

For to use Macros? I don`t have a clue. How does macro unprotects the sheet? Is there needed to do any codeing?
Can you please show/writen how would be this word transfering from A1 to A2!? It has to be Automating movement of data from one cell to another.
The word that I enter into A1, has stay from then on in A1 as well.

[ChemistB]

This code would be imputted for the worksheet that you have protected and want the name transferred to A2. First you will need to unlock cell A1 and protect your worksheet.

Then
Right click on the spreadsheet tab and "View Code"
Paste this code into the window that opens (VBA Editor) and then close the editor (no need to save, it's linked to your workbook). I'm only a novice at VBA but this works.

Please Login or Register  to view this content.

Save it. Then try inputting into A1. Good luck!

[Mitja Bonca]

YOu mean that I put into A2, not A1?
How do I do that? I can not just copy paste.
Sorry, I know basics of excel, I am not an expert.

[ChemistB]

Do you have an example workbook that you can post here ("manage attachments")? It might be easier to describe if I can see the workbook.

[Mitja Bonca]

Ok, here you go. It`s unprotected and I added some explanations in english, for easier understanding.
Just that it`s not A1 to A2 name transfer but G3 to J130.
Take a look.
thx

[ChemistB]

Got it. G3 is part of merged cells which complicates things a bit but will work on it.

[ChemistB]

See how you like this. On the Prisotnost Navpično Sheet, I unlocked cells G3:P3 and F6:Q129. Then I protected the spreadsheet (the other spreadsheet is still unprotected.

You can look at the code by right clicking on Prisotnost Navpično tab and "View Code"

Save this workbook before using it. Once you use it, G3 becomes locked so it cannot be erased. (You'll need to unprotect the sheet to change it back). I didn't put in password protection. If you want to use password protection, you'll need to modify the code.

[Mitja Bonca]

Will you do that what I want for me?

[ChemistB]

So that means it worked?
Okay, if you want to save with password,
1. Open up your template- the one I sent you.
2. Unprotect the spreadsheet (Tools>Protection>Unprotect Sheet)
3. Right click on the tab and "View Code"
4. Modify these two lines of code

Please Login or Register  to view this content.

Replace the password in quotes with a password of your choice (keep the quotes)
Close the VBA editor with the code (no need to save)

On your worksheet, Tools > Protection>Protect Sheet
and enter your password you have chosen.
Then save it. Then try it.
Let me know if this works or if you have problems.

[Mitja Bonca]

thx for your help,
but when I try to use your version, I can not write, cause it is proteced. If I want to write, 1st of all I need to unprotect the sheet. But when I do that, I can write where ever I want, and the name entered into G3 is not transfered into J130. What am I doing wrong?
and btw, I don`t need a pw for the sheet.

[Mitja Bonca]

I have save the sheet, but when I want to use it (to put a name into G3 cell, it says it is protected). If I want to use it, I need to unprotect the whole sheet, but then the entered name into G3 is not transfered into J130.
How you do this that is starts working?

[ChemistB]

Unprotect the sheet.
Go to G3 and Format Cells>Protection
Uncheck the Locked box. Hit okay.
Protect the worksheet
Save this Workbook.

Once you enter a name in G3, it will be locked. You need to go back to your original that you just saved in order to create a new workbook for each person. Hope that helps.

[Mitja Bonca]

Can you please help me out with this one:
I want to put hours of 1st working day into cell A1 (for example). If there is up to 8 hours, it`s all ok. The sum of days goes to let`s say C1. If there in a 2nd day I do 10 hours, I would like that 8 hours go to C1 and 2 hous to D1 (how overtime). I can not find the right code for it.
Please for some advice.
thx

[mewingkitty]

Please Login or Register  to view this content.

in C2 for regular hours.


and

Please Login or Register  to view this content.

in D2 for overtime hours.

You can enter the number of hours down column A, and it will total your regular and overtime hours in cells C1, and D1.

Note that the range added is only A1:A10. You may want to lengthen/shorten that for use with your sheet.

[Mitja Bonca]

Thx for a reply.
those A1:A10 means that you pick all the cells from A1 to A10.
But I have to pick cells like A1 then A5, then A9, then A13, and so on (for 31 days). Every 5th cell is the same item. So I can not use A1:Axx).
Is there any other way how to pick cells that I want?
btw, how do I sum cells which already have a equation inside?