Additional Conditional Formatting Question

S

Sinfante

I have a budget with a master worksheet and each month as a separat
worksheet all within one workbook.

As I posted before:
I am trying to get a buget with carryover overages or underages
i.e.
B2= budgeted amount
C2= actual spent
D2= overage or underage

The Formula Posted by Max worked wonderfully
D2: =IF(OR(ISBLANK(C2),C2<=0),"",B2-C2)

I copied this into each month's D2 cell and dragged it down to all th
cells in that column so the formula would be copied to all of the item
in the budget

Now what I am trying to do is to keep a running total on the maste
sheet in F2
where F2 would = August D2 + September D2 + October D2 + November D2
December D2

I have tried this, but I cannot seem to get it to work. The outcome i

#Value!

I am wondering if it is because there are no numbers in all of the D
cells for each month yet.

If this is the reason, can the formula above for D2 be altered to hav
an output of 0 if there is no overage or underage?

or is there another way to do this?

Thanks again in advance
 
D

David McRitchie

Hi Sinfante,
There is an example at
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
if you're not fussy about the actual appearance, it is designed
to make the generation easy.

For what you have you could use a 3-D formula, instead of
something like: your attempt modified to include the exclamation after sheetname.

F2: = August!D2 + September!D2 + October!D2 + November!D2 + December!D2

which might be
F2: =2004_08!D2 + 2004_09!D2 + 2004_10!D2 + 2004_11!D2 + 2004_12!D2

but as a 3-D reference as long as you have the tabs in order
F2: =2004_08:2004_12!D2

For some examples of using sheetnames in formulas:
http://www.mvps.org/dmcritchie/excel/sheets.htm
 
M

Max

D2: =IF(OR(ISBLANK(C2),C2<=0),"",B2-C2)

Looks like we have to add a check for "text" in C2 as well ..
(text in C2 would throw up > #Value! errors)

Try in D2: =IF(OR(ISBLANK(C2),ISTEXT(C2),C2<=0),"",B2-C2)
 
S

Sinfante

Thanks to both for the help. As I am sure You can tell I am not good a
this kind of formulating.

I will try to give a more information and then I hope a solution wil
be evident.

The Workbook is Budget2004
There are a total of 6 worksheets listed below in order

(1ST) MASTER, (2ND) AUGUST, (3RD) SEPTEMBER, (4TH) OCTOBER, (5TH
NOVEMBER, (6TH) DECEMBER

The master is where I try out my formulas and am trying to keep
running total of overages and underages

In each worksheet the ROW 1 is a header explaining what is in eac
column

column A is the PAYEE,
column B is the Budgeted amount,
Column C is the amount actually payed,
Column D is the over/under amount,
Column E is blank for separation purposes,
Column F is where I want a running total of the over/under for eac
month to add up.

In every worksheet, column A contains a list of names
In every worksheet, column B contains a list of amounts

D2 has the following formula (this is copied down the entire column an
all D columns in each worksheet has the same formula)

=IF(OR(ISBLANK(C2),ISTEXT(C2),C2<=0),"",B2-C2)

-this is working still for the original problem I had in the origina
"CONDITIONAL FORMATTING" post-

F2 has the following formula (this is copied down the entire column)

=August!D2+September!D2+October!D2+November!D2+December!D2

the output I still get is #VALUE!

Again I appreciate any help you can give
 
M

Max

F2 has the following formula (this is copied down the entire column)
=August!D2+September!D2+October!D2+November!D2+December!D2
Click to expand...

As Dave M says, SUM() will ignore text.

So assuming the sheets: August and December
are the "book-end" sheets, i.e. the "1st" and the "last" sheets
you could put in F2 in sheet: Master, the equivalent formula:
=SUM(August:December!D2)
and copy down
 
M

Max

Clarification:
The sheet: Master is assumed to be located "outside" of the
the sheets: August to December
 
D

David McRitchie

The answers posted have been understood and accepted by
the poster. But I want to interject a couple of additional comments.

I would suggest using worksheet names like 2004_08
instead of August so that you can have more than one year and
so that you can sort the worksheet tab names with a macro i.e.
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets

The questions are not really related and certainly in retrospect should
have been posted as two separate questions so that people looking
for solutions will be better able to match what they want with the subject title.
Certainly putting related questions together can be helpful when
someone else also needs or will find that they need help with some
related aspects of a question. But putting unrelated questions
can end up getting only one of the questions (the less important one,
of course) answered, or people who might have answered one part
might avoid the question for lack being able to answer both parts.
 
S

Sinfante

Thank you for your suggestion about the names of the sheets. That is a
excellent idea.

I don't really understand the rest of your post as to the two question
that should have been separated. I had originally posted one questio
and was answered. When I realized I had a second question I poste
another new topic with reference to the previously asked and answere
question, just in case that helped to clarify the second question i
the second post

I am a novis Excel user and found this website on a google search fo
answers about conditional formatting. I did not realize I wa
incorrectly posing my questions. I am truly grateful for the help
received and the suggestions made to make my workbook easier to use.


David said:
*The answers posted have been understood and accepted by
the poster. But I want to interject a couple of additiona
comments.

I would suggest using worksheet names like 2004_08
instead of August so that you can have more than one year and
so that you can sort the worksheet tab names with a macro i.e.
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets

The questions are not really related and certainly in retrospec
should
have been posted as two separate questions so that people looking
for solutions will be better able to match what they want with th
subject title.
Certainly putting related questions together can be helpful when
someone else also needs or will find that they need help with some
related aspects of a question. But putting unrelated questions
can end up getting only one of the questions (the less importan
one,
of course) answered, or people who might have answered one part
might avoid the question for lack being able to answer both parts.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
Click to expand...
 
D

David McRitchie

You're welcome. I'll try to explain the rest of the post better.

I don't see that the "new" question has anything to do the
subject title. When people search newsgroups the subject
is used as part of the search or to visually look to see which
threads look the most promising.

If you look at the following link tomorrow or perhaps 12 hours
from now you would see your thread and the subthreads as
would be seen in a newsreader. The web page you are using
to submit to a newsgroup does not put your response to the
correct part of the thread, a little confusing to newsgroup readers.
http://google.com/[email protected]

Google has a web page search that you are used to using.

Google also has Google Groups which searches newsgroup
postings. You can read more about newsgroup searching at
http://www.mvps.org/dmcritchie/excel/xlnews.htm
But you don't want to particularly reply through Google Groups
because of the 12 hour delay.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm



Sinfante > said:
Thank you for your suggestion about the names of the sheets. That is an
excellent idea.

I don't really understand the rest of your post as to the two questions
that should have been separated. I had originally posted one question
and was answered. When I realized I had a second question I posted
another new topic with reference to the previously asked and answered
question, just in case that helped to clarify the second question in
the second post

I am a novis Excel user and found this website on a google search for
answers about conditional formatting. I did not realize I was
incorrectly posing my questions. I am truly grateful for the help I
received and the suggestions made to make my workbook easier to use.


David said:
I would suggest using worksheet names like 2004_08
instead of August [clipped]
The questions are not really related and certainly in retrospect
should
have been posted as two separate questions so that people looking
for solutions will be better able to match what they want with the
subject title.
Click to expand...
Click to expand...
 
M

Max

David McRitchie said:
.. I don't see that the "new" question has anything to do the
subject title. When people search newsgroups the subject
is used as part of the search or to visually look to see which
threads look the most promising ...
Click to expand...

The "minor" point is probably because the subject line in both posts
by the OP mentioned: "Conditional Formatting" which wasn't
really spot-on as a key descriptor of the posts, and might have been
a bit misleading. But admit at times it can get pretty tough
to grab the correct core phrases to best describe one's post in a
couple of words in the subject line <bg>

"Conditional Formatting" actually refers to something else -
viz. applying formatting to cell(s) to auto-monitor the cells
and display as visual alerts should certain cell conditions
be met / triggered. For e.g.: Cell's fill color will turn red,
and/or font will turn red & bold, if value in cell exceeds or
drops below a certain set target value, etc.

Conditional formatting is set via the menu:
Format > Conditional Formatting ..
 
Top