Formula omits adjacent cells error message
 

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.

Formula omits adjacent cells error message
 

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

HTH,
Elkar



"Nevets" wrote:

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.

Formula omits adjacent cells error message
 

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.

"Elkar" wrote:

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

HTH,
Elkar



"Nevets" wrote:

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.

Formula omits adjacent cells error message
 

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

On Fri, 18 Jan 2008 13:20:00 -0800, Nevets
wrote:

Trying to set up a spreadsheet for multiple users, that is relatively
foolproof.

Formula omits adjacent cells error message
 

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


On Fri, 18 Jan 2008 15:37:01 -0800, Nevets
wrote:

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.

"Elkar" wrote:

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

HTH,
Elkar



"Nevets" wrote:

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.