clearing cells w/out clearing the formulas, formats

[Tom F]

I have a schedule to demonstrate. The start time and end time are entered by the user after which the remaining information is calculated and displayed on the worksheet.

A blank worksheet is desired to begin without deleting the formulas or formats. Is it possible to clear the cells without clearing the formulas within the cells? If so, how?

 

[Derek]

Hi Tom F

If you do not want your formulas to display zeros because you have deleted the constants they act on (as suggested above), you might like to try custom formatting your formula cells so that zeros and minus numbers are not displayed.
For example, select the cells that contain formulas that return times, go to Format / Cells / Number / Custom and type in [h]:mm;;
The 2 semi-colons ensure that zero and minus numbers are not returned. You can use the 2 semi-colon method on most other formats too.

hope this helps
regards
Derek

 

[Yogi Anand]

Good call Derek!

Regards

Yogi

 

[Anne Troy]

I'll have a look at your spreadsheet.

Anne@TheOfficeExperts.com

 

[maxflia10]

Here's my attempt. A1 and B1 house start and end time. C1 is the time elapsed. C1 stays blank until both A1 and B1 are entered. Missing one cell, still produces a blank in C1. To start a new simple delete all times in column A and B

Book2
	A	B	C	D
1	10:30	12:00	1:30
2	11:00
3		13:00
4	11:00	13:00	2:00
Sheet1

By the way, got the formula from Mark W. if you use it, credit to Mark

 

[Tom F]

Thanks for your many replies. I am a fledgling at Excel and learning a lot. I am still working on this and have emailed the file to a few kind people who volunteered to take a look at it.

Maxflia10, you hit the nail on the head on your last sentence when you said, "To start a new simple delete all times in column A and column B." That is precisely what I am trying to do. I wish to delete the times in the respective cells without deleting the formula.

With the "giving" nature of the members of this board and persistence, the solution will come.

That reminds me of the saying, "We make our living by what we get and we make our life by what we give."

Many thanks to all, <fingers crossed>
Tom F

 

[Iridium]

I might be missing the point but how about saving the 'blank' sheet containing the formulae and formats as a template (.xlt)?

 

[Tom F]

Finally, I got it.

My tunnel vision got in the way and the forest was in the way of the trees.

Thanks to everyone who helped. I appreciate it sincerely.

One more question.

What is the proper syntax for these two statements?

If(count(a1,b1)=2) and if(x=3) then <true>,<false>

Many thanks to all,
Tom F

 

[Tom F]

Finally, I got it.

My tunnel vision got in the way and the forest was in the way of the trees.

Thanks to everyone who helped. I appreciate it sincerely.

One more question.

What is the proper syntax for these two statements?

If(count(a1,b1)=2) and if(x=3) then <true>,<false>

Many thanks to all,
Tom F

 

[Yogi Anand]

On 2002-10-21 07:43, Tom F wrote:
...
One more question.

What is the proper syntax for these two statements?

If(count(a1,b1)=2) and if(x=3) then <true>,<false>
...

Hi Tom F:

use ...
=if(and(count(a1,b1)=2,x=3),ifTRUE,ifFALSE)

Regards!

Yogi

 

[Anne Troy]

Tom actually had a timesheet-sort of setup. However, most all of the cells were dependent on the entries of each other, except for two cells.

So I did an =IF(OR(ISBLANK(A1),ISBLANK(A2)),"",Formula) on virtually all the cells.

Then I gave him a little macro button that clears just those two cells.

He says it worked great.