Formula omits adjacent cells error message

N

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(C13:D13)=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.
 
E

Elkar

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

HTH,
Elkar
 
N

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.
 
G

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top