About Us

SLP

That is an idea. Thanks.

"Richard" wrote:

sounds as though the best bet would be to create a macro to go through the
cells and enter a 0 for those that are blank

"SLP" wrote:

Hi, The file is too large to be sent as an attachment.

What the formula does is: Rate Per Month * Hours Per Day - Vacation Hours
Scheduled * Percent of Time for the Cost Center (as folks can be in multiple
Cost Centers)

So I should see a value if rate, hours per day, percent is filled in whether
or not they take vacation.

"Richard" wrote:

if you can send me an example of the workbook I can sort this, it doesn't
sound difficult only I need to see it. you'll also have to tell me what
you'd like to see instead of #value. If you are happy to do this send it to

"SLP" wrote:

If I go to the vacation schedule sheet and enter a zero in I6 it works fine.
If I then go back to the vacation schedule sheet and delete the zero it works
fine. (It being my formula). I see what the person's true salary is for the
month I am on.

Any other thoughts? I guess I can just have my end users always enter a
zero if someone is not scheduled to take vacation in a given month but they
will moan about having to enter zeros.

Funny thing it is not happening on every row.

"Richard" wrote:

If you want something to appear even for the "blank" cells then you have to
enter something in the quotes after true, in the suggested formula. for
example

=if(iserr(ROUND(EmployeeData!AM6*(EmployeeData!$P6 *'Emp
Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2))=true,"This Cell
IS Blank",ROUND(EmployeeData!AM6*(EmployeeData!$P6*'E mp
Calendar'!F6-'Vacation Schedule'!I6)*ProgramOverview!$AR6,2))

will return the comment This cell is blank. if you don't want a text
comment enter a different formula and remove the ""

"SLP" wrote:

Thanks but that formula results in no value. I did an =isblank and you are
right, there is something in the cells that look blank which is causing the
error. Any other ideas?

"Richard" wrote:

It looks as though some of the cells that are being used for calculation are
not empty, but contain a space " ", which cannot be rounded.

A simple solution to this would be to use the following formula which
checks for the error and saves you having to put 0 in the blank cells

=if(iserr(ROUND(EmployeeData!AM6*(EmployeeData!$P6 *'Emp
Calendar'!F6-'Vacation
Schedule'!I6)*ProgramOverview!$AR6,2))=true,"",ROU ND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation
Schedule'!I6)*ProgramOverview!$AR6,2))

Richard

"SLP" wrote:

Hi. I'm stumped. I have a formula:

=ROUND(EmployeeData!AM6*(EmployeeData!$P6*'Emp Calendar'!F6-'Vacation
Schedule'!I6)*ProgramOverview!$AR6,2)

Sometimes I works fine and other times I have to go to the VacationSchedule
sheet and enter a zero when there isn't a number in the cell in order to not
have the #Value error show.

Any ideas what it could be? Thanks.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Dates not consistent	Nev Wood	Excel Discussion (Misc queries)	3	September 30th 08 01:43 PM
Sub-Totals not consistent	Tia	Excel Discussion (Misc queries)	0	March 12th 07 08:53 PM
Dir command not consistent	Robert_L_Ross	Excel Programming	1	June 30th 06 10:17 PM
Keep consistent between two worksheets	Hank	Excel Discussion (Misc queries)	1	June 29th 06 04:04 AM
Excel formula to make AAA-BBB equal to/consistent with BBB-AAA	AliceJDavidson	Excel Worksheet Functions	1	May 9th 05 09:30 PM

Menu

About Us