Excel Formulas

Robm1955

I have created a spreadsheet for a sort of timesheet. I have a start time, end time, hours worked (which is a formula based on start time and end time), and rate of pay, and expected payment (which is also a formula). To make it look tidy, and hide error messages which appear when all cells are not completed, I have put the main formulas in cells like AA,AB,AC etc, and then where the data goes, put the formula eg =IF(E5="","",AA5). This worksheet is for 1 month, so I have twelve of these. Every one is the same, apart from the dates obviously. Someone has suggestion I should put all the workings (the AA,AB, etch) cells in another worksheet. My question is, how do I reference these for all the other worksheets? If that makes any sense.

paddyrg

You can't reuse the same AA,AB etc cells to contain data or formulas for multiple sheets, so that 13th sheet has to have 12 sets of your formulas. Referencing is just done with the sheet name and exclamation mark eg Sheet13!AB6 or January!AA3:AA37

You might also want to look up the existing functions isblank(), iserror() which may give you easier ways of displaying your data inline without needing to use extra cells - that would be my approach anyway - fewer links fewer things to go wrong!

Robm1955

Thanks Paddyrg. Just after I posted, I started messing with formulas in the13th sheet, then I realized that the only way would be to have 12 sets of formulas. I'm actually doing it for my daughter, and she said someone at work said they would put the data on a seperate sheet. Can't see the problem in having them in AA,AB etc. myself. I'll look at function isblank() and iserror().

Robm1955

I ended up using isblank(), which worked perfectly. Just one final question now. How do I protect all the formulas in a Workbook. I thought I knew, but it doesn't seem to have worked. I selected all the tabs in the workbook, and followed the procedure for protecting a worksheet, but I can still type in where the formulas are.

bod1467

On each worksheet, Ctrl-A to select all cells, right-click and select Format Cells. On the Protection tab make sure the Locked box is checked. Then for each worksheet, right-click the worksheet and select Protect Worksheet and choose what you want users to be able to do.

This is for Excel 2007 ... other versions will be similar but the method may be slightly different.

Robm1955

Thanks Bod, I knew this method. I just wondered if it could be done as a Workbook. Doing it this way means a password for each Worksheet.

bod1467

http://office.microsoft.com/en-001/excel-help/password-protect-worksheet-or-workbook-elements-HP010078580.aspx

http://office.microsoft.com/en-001/excel-help/secure-a-workbook-with-a-password-HP001112410.aspx

Prothet_of_Doom

Try this.

In a cell with a formula, select CUT (Ctrl X)
Open sheet 2
Select Paste (Ctrl V)
Does that not work for you.

Tell me what is difficult about that?

Now try it but paste is into a completely different File.

Now with both files open, change the name of the first file.

Now close both files.

Open the second file.

Enable the links. Point the link location to the first file.

Now try it again, but move the first file to a new location and rename it when you've moved it.

Open the second file and enable the links. Point the link location to the new location and new name of the first file.

Do you feel good ?

Robm1955

Prothet_of_Doom wrote: »

Try this.

In a cell with a formula, select CUT (Ctrl X)
Open sheet 2
Select Paste (Ctrl V)
Does that not work for you.

Tell me what is difficult about that?

Now try it but paste is into a completely different File.

Now with both files open, change the name of the first file.

Now close both files.

Open the second file.

Enable the links. Point the link location to the first file.

Now try it again, but move the first file to a new location and rename it when you've moved it.

Open the second file and enable the links. Point the link location to the new location and new name of the first file.

Do you feel good ?

Sorry, but I can't see what this relates to! Isblank worked for me, but thank you for your reply.