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