Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Omits Adjacent Cells | Excel Worksheet Functions | |||
Too many formatted cells error message | Excel Discussion (Misc queries) | |||
Too many formatted cells error message | Excel Discussion (Misc queries) | |||
error message: merged cells must be same size | Excel Worksheet Functions | |||
How to sum a row of cells containing an error message to overlook | Excel Worksheet Functions |