Formula omits adjacent cells error message

[Nevets]

Trying to set up a spreadsheet for multiple users, that is relatively
foolproof.
Cell B13 has an hourly rate for a service.
Cells C13 and D13 have on- and off-site times for the service, both
formatted as hh:mm.
Cell E13 is the total on-site hours, formatted as a number, with the
following formula: =((D13-C13)-INT((D13-C13)))*24. This gives a value in
hours.
F13 is a variable numbers of hours charged for prep/travel time.
I want G13 to be the number of hours I bill for, with a minimum of 4 hours
on-site, and including the prep/travel time. Earlier, I asked for help on
how to do this, and got a few suggestions. None of them worked, but I
eventually got the following to work:
=IF(COUNT(C1313)=2,F13+MAX(E13,$B$35),0).
It all works brilliantly, with one small problem. As soon as I enter a
value in B13, I get an error message in G13 as follows:
The formula in this cell refers to a range that has additional numbers
adjacent to it.
I can tell the program to ignore the error, but I'd rather figure out a way
to not have the error appear at all.

 

[Elkar]

TOOLS
OPTIONS
Error Checking Tab
Uncheck "Formula omits cells in region"
OK

HTH,
Elkar

 

[Nevets]

That works for my computer, but as I mentioned, I want this to be a template
that multiple people can use. I don't want to have to have each of them
change their Options in order for error messages not to appear. Especially
since the computer skills of several of them are a bit lacking. I would
rather have a formula that works without the error being generated in the
first place. Is there another way?
Thanks.

 

[Gord Dibben]

See Elkar's post for a solution to your pop-up.

I just want to address your attempts at fool-proofing.

Just when you have something fool-proof, along comes another generation of fools
with a whole new set of standards.


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Another way is turn off the Extend List when users activate your workbook and
turn it back on when you workbook is de-activated.

Private Sub Workbook_Activate()
Application.ExtendList = False
End Sub

Private Sub Workbook_Deactivate()
Application.ExtendList = True
End Sub

Copy and Paste into Thisworkbook Module.


Gord Dibben MS Excel MVP